Conditional format whole rows with more than 3 conditions

[sesg77]

I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.

I need to highlight the row if column W has a
G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)

Any ideas on how I can do this?

 

[jeffreybrown]

Something like this

 

[sesg77]

Hi

Thanks for the reply. However I need to highlight the whole row and this formula only highlights the cell.

 

[jeffreybrown]

Try

Sub HighlightRows()
    Dim cl  As Range
    Dim LR  As Long
    Dim i   As Long
    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Range("W" & .Rows.Count).End(xlUp).Row
        For i = 2 To LR
            If Range("W" & i).Value = "G" Then
                .Range(.Cells(i, 1), .Cells(i, 23)).Interior.ColorIndex = 35
            ElseIf Range("W" & i).Value = "R" Then
                .Range(.Cells(i, 1), .Cells(i, 23)).Interior.ColorIndex = 3
            ElseIf Range("W" & i).Value = "Y" Then
                .Range(.Cells(i, 1), .Cells(i, 23)).Interior.ColorIndex = 36
            ElseIf Range("W" & i).Value = "O" Then
                .Range(.Cells(i, 1), .Cells(i, 23)).Interior.ColorIndex = 44
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

This will highlight from row 1 to row 23

 

[sesg77]

Thanks for this code. It seems to work, but it does not enable when the letter is changed in column "W", the formatting doesn't change automatically. Is there a way of doing this?

 

[jeffreybrown]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("W:W")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Select Case Target.Value
    Case "G"
        Range(Cells(Target.Row, 1), Cells(Target.Row, 23)).Interior.ColorIndex = 35
    Case "R"
        Range(Cells(Target.Row, 1), Cells(Target.Row, 23)).Interior.ColorIndex = 3
    Case "Y"
        Range(Cells(Target.Row, 1), Cells(Target.Row, 23)).Interior.ColorIndex = 36
    Case "O"
        Range(Cells(Target.Row, 1), Cells(Target.Row, 23)).Interior.ColorIndex = 44
    End Select
End Sub

This is worksheet code >> right click on the sheet tab >> View Code >> Paste on right side of screen.

http://www.rondebruin.nl/code.htm

 

[sesg77]

This works fab. Many thanks

 

[jeffreybrown]

You're welcome and thanks for the feedback.