Conditional formatting entire row based on values in four cells

[chriswalker32]

Hi all - newbie here (having used the forum numerous time before for advice)

I would really apprecaite it if someone could assist me with the following query regarding conditional formatting in Excel.

I am currently working on a timetable within Excel which requires row colour to be changed to reflect four different authorisation points i.e.

If Person 1 authorises a document by entering a date in cell K2, row 2 changes to yellow;
If Person 2 authorises a document by entering a date in cell L2, row 2 changes to orange;
If Person 3 authorises a document by entering a date in cell M2, row 2 changes to blue; and
If Person 4 authorises a document by entering a date in cell N2, row 2 changes to green.

All rows need to be white by default until any level of authorisation occurs.

At present, I am able to cater for three conditions using conditional formatting however it would be really useful to cater for all four conditions using VBA code. I have little experience in code writing and would really appreciate some information on how to use VBA to allow for these four conditions.

Happy to send over the excel spreadsheet in question if I have not been clear!

 

[VoG]

Hi. Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target.Value) Then
    Select Case Target.Column
        Case 11: Target.EntireRow.Interior.ColorIndex = 6
        Case 12: Target.EntireRow.Interior.ColorIndex = 44
        Case 13: Target.EntireRow.Interior.ColorIndex = 5
        Case 14: Target.EntireRow.Interior.ColorIndex = 4
    End Select
End If
End Sub

 

[chriswalker32]

Hi VoG,

Thank you - thats exactly what I was after!
Is there a way to ensure that the rows remain white if no authorisation has occured?

Thanks again!

 

[charleymax]

Case Else
icolor = 2

 

[VoG]

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target.Value) Then
    Select Case Target.Column
        Case 11: Target.EntireRow.Interior.ColorIndex = 6
        Case 12: Target.EntireRow.Interior.ColorIndex = 44
        Case 13: Target.EntireRow.Interior.ColorIndex = 5
        Case 14: Target.EntireRow.Interior.ColorIndex = 4
    End Select
Else
    If Target.Column >= 11 And Target.Column <= 14 Then Target.EntireRow.Interior.ColorIndex = xlNone
End If
End Sub

 

[chriswalker32]

Thanks!

one final thing - if , for example, AG is the last column in the table is there any way to ensure that only a2:ag2 is coloured?

 

[VoG]

Try like this

Range("A" & Target.Row).Resize(, 33).Interior.ColorIndex = 6

 

[HYKE]

Why for some reason this code is not working on my sheet? I even open a new sheet and pasted this code, but still there is no event bieng triggered.....

 

[VoG]

Why for some reason this code is not working on my sheet? I even open a new sheet and pasted this code, but still there is no event bieng triggered.....

Make sure that events are enabled. In the code window press ALT + G then in the Immediate Window type

Application.EnableEvents=True

and press Enter.

 

[HYKE]

Hi Vog,

This is how I put the enable event code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsDate(Target.Value) Then
    Select Case Target.Column
        Case 17: Target.EntireRow.Interior.ColorIndex = 6
        Case 19: Target.EntireRow.Interior.ColorIndex = 44
        Case 31: Target.EntireRow.Interior.ColorIndex = 4
        'Case 14: Target.EntireRow.Interior.ColorIndex = 4
    End Select
Else
    If Target.Column >= 17 And Target.Column <= 31 Then Target.EntireRow.Interior.ColorIndex = xlNone
End If
Application.EnableEvents = True
End Sub

However the ALT+G thing I don't know how..