Format cell color on change, how to change the color in other columns as well?

**s80NL** · 12-27-2007, 11:58 AM

Currently I've got the following (found) piece of code which colors the cell on row E on changing the value.

Which adjustments need to be made to the code in order to make it color other cells on the same row as well (e.g. columns A to C and H)?

Cheers for all your help in advance!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

    Dim rng As Range

    Set rng = Intersect(Target, Range("E:E"))
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim c As Range
        For Each c In rng
            Select Case c.Text
            Case "Upcoming"
                c.Interior.ColorIndex = 34
            Case "Open"
                c.Interior.ColorIndex = xlNone
            Case "Pending"
                c.Interior.ColorIndex = 22
            Case "On Hold"
                c.Interior.ColorIndex = 22
            Case "Finished"
                c.Interior.ColorIndex = xlNone
            Case "Cancelled"
                c.Interior.ColorIndex = 3
            Case "Order Hardware"
                c.Interior.ColorIndex = 44
            Case "Awaiting Order"
                c.Interior.ColorIndex = 45
            Case "SPECIAL"
                c.Interior.ColorIndex = 43
            Case "R&M initiated"
                c.Interior.ColorIndex = 43
            Case Else
                c.Interior.ColorIndex = xlNone
                Exit Sub
            End Select
        Next c
    End If

End Sub

**shg** · 12-27-2007, 01:43 PM

Perhaps this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng     As Excel.Range
    Dim cell    As Excel.Range
    Dim iCI     As Long

    Set rng = Intersect(Target, Columns("E"))
    If rng Is Nothing Then
        Exit Sub
    Else
        For Each cell In rng
            Select Case cell.Text
                Case "Upcoming":       iCI = 34
                Case "Open":           iCI = xlNone
                Case "Pending":        iCI = 22
                Case "On Hold":        iCI = 22
                Case "Finished":       iCI = xlNone
                Case "Cancelled":      iCI = 3
                Case "Order Hardware": iCI = 44
                Case "Awaiting Order": iCI = 45
                Case "SPECIAL":        iCI = 43
                Case "R&M initiated":  iCI = 43
                Case Else:             iCI = xlNone
            End Select
            Set cell = Intersect(cell.EntireRow, Union(Columns("A:C"), Columns("H")))
            cell.Interior.ColorIndex = iCI
        Next cell
    End If
End Sub