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!

 

[HYKE]

These are the other codes I have in the sheet.

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
 
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
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveCell.EntireRow.Select
End Sub

maybe the other codes here are affecting it...

 

[VoG]

Sorry it should be CTRL + G to open the Immediate window. or View > Immediate window.

 

[HYKE]

Hi Vog,

it is finally working now..Thanks..Just one last thing too though..

    Select Case Target.Column
        Case 17: Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 6 cell 36="PENDING"        Case 19: Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 44 cell 36=@ATPLANT"
        Case 31: Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 4=cell 36="CLOSED"
        Case 17: Target.row="" then interior.colorindex=red and cell 36.value="noETA"    End Select

Can you work the code for me to do the following additions?

Thanks!!!

 

[VoG]

Maybe like this

Select Case Target.Column
    Case 17
        Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 6
        Cells(Target.Row, 36).Value = "PENDING"
    Case 19
        Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 44
        Cells(Target.Row, 36).Value = "ATPLANT"
    Case 31
        Range("A" & Target.Row).Resize(, 34).Interior.ColorIndex = 4
        Cells(Target.Row, 36).Value = "CLOSED"
End Select

 

[HYKE]

Thanks Vog--I'll just try to figure out the other one...Thanks Again