Drawing Register latest issue indicating problems

[hillsee]

Hi everyone,

I have attached an excel spreadsheet of a drawing register we use within our architectural practice. I have managed to colour a coloumn cell fill yellow, dependent on the latest drawing issue using conditional formating.

However I would also like the DWG Number, Description and any cells to the left of any of the cells which are indicated to get issued to have a cell fill of yellow.

For instance,

on row 12 I would like AAA 01 002, Ground Floor Proposed, b, d to be coloured yellow,
similarly on row 14 I would like AAA 01 005, Ground Floor Ceiling, d to be coloured yellow

I apologise for not explaining myself correctly, it might be best you have a look at the attached template.

Would this be possible using conditional formating or would I have to use VB code, and if so whwat would the VB be?

Thanks in advance.

Hillsee

 

[noboffinme]

Hi hillsee

Here's some VBA that doesn't have the usual 3 Cond Format limitations;

This code works on column 'A' & you add the Cells value (in place of where I have put 'yes', 'value' & 'ENCYCLOPEDIA' ) you want to change the colour to.

You could change it to format column 'B' if the interior colour of column 'A' is yellow for example;

-------------------------------------

Sub cond_format_multi()
Dim i As Integer
Dim ncol As Long


For i = Sheets("Sheet1").Range("A65536").End(xlUp).Row To 2 Step -1


If ActiveSheet.Cells(i, 1).Value = "yes" Then
ActiveSheet.Cells(i, 1).Interior.ColorIndex = 4
End If

If ActiveSheet.Cells(i, 1).Value = "value" Then
ActiveSheet.Cells(i, 1).Interior.ColorIndex = 5
End If
If ActiveSheet.Cells(i, 1).Value = "ENCYCLOPEDIA" Then
ActiveSheet.Cells(i, 1).Interior.ColorIndex = 8
End If

Next i

End Sub

-------------------------

HTH

 

[hillsee]

Hi Noboffinme,

Thanks for the help / code, unfortunately I dont understand VB enough to make the code work.

Thanks for all your help... I think I am just trying to do too much in excel..

Hillsee