Changing a row colour automatically based on a cell input.

[Guest]

Is there a method whereby I can have a input a date in a cell and it will automatically change the row colour? I am tracking a workflow process.

For example,
if I input a date into cell C7, then row 7 would change all cells to be colour 'LIGHT BLUE'.
if I then, input a date into cell D7, then row 7 would change all cells to be colour 'YELLOW'.
if I then, input a date into cell E7, then row 7 would change all cells to be colour 'GREEN'.
etc, etc...

Any ideas on the best way to do this?

 

[Rob van Gelder]

Highlight Row 7
Select Conditional Formatting from the Format menu

Condition 1: Formula Is: =NOT(ISBLANK($E7))
Colour = Green
Condition 2: Formula Is: =NOT(ISBLANK($D7))
Colour = Yellow
Condition 3: Formula Is: =NOT(ISBLANK($C7))
Colour = Light Blue


You'll need to use VBA to get any more Conditional Formats than three.


--
Rob van Gelder - http://www.vangelder.co.nz/excel

Is there a method whereby I can have a input a date in a cell and it will

automatically change the row colour? I am tracking a workflow process.

 

[Rob van Gelder]

Yet another way would be to catch events as the Worksheet Changes

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 3, 4, 5
If Not (IsEmpty(Cells(Target.Row, 5).Value)) Then
Rows(Target.Row).Interior.ColorIndex = 4
ElseIf Not (IsEmpty(Cells(Target.Row, 4).Value)) Then
Rows(Target.Row).Interior.ColorIndex = 6
ElseIf Not (IsEmpty(Cells(Target.Row, 3).Value)) Then
Rows(Target.Row).Interior.ColorIndex = 8
Else
Rows(Target.Row).Interior.ColorIndex = xlColorIndexNone
End If
End Select
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel

Is there a method whereby I can have a input a date in a cell and it will

automatically change the row colour? I am tracking a workflow process.

 

[Guest]

Rob,

Many thanks for that, it works a treat!

Mant thanks!

Steve