Thread: Conditional Formatting with VB across a table

Hi

I've got a table of information on a tab that is generated from data input to other tabs in my workbook. I've set up conditional formatting so that the fill colour changes automatically from green to yellow, orange or red depending on the score achieved. I've been asked to add a grey colour for dates that haven't been completed. I've got the following code that will change the colour based on the date (in the two above merged cells) being incomplete

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Select Case Range("A1:B1")
Case Is = "00/01/1900"
Range("A2:B2").Interior.ColorIndex = 15
End Select
End Sub


Does anyone know if there is a way to copy this across the cells in the entire table quickly, or if I'm just going about all in the completely wrong way? Any help or advice appreciated.

Excel has built in conditional formatting you could use that.

Dim myRange as Range
Dim thisCell as Range

Set myRange = Sheets("Your Sheet Name").Range("A1:Z500") 'Set Z500 to the lower right of your table.

For Each thisCell in myRange
     Select Case thisCell.value
          Case is = "00/01/1900"
               thisCell.Interior.ColorIndex = 15
    End Select
Next thisCell

This will look at each individual cell. You can put that in the same event handler you are in.