Change the colour of a Row when a condition changes in a cell

**Philw56** · 11-02-2011, 08:00 PM

Hello

I have a workbook with several sheets in Excel 2003 and was using conditional formatting to change the colour of a Row when a certain condition was met. As an example if I selected "C" from a drop down list it would change the colour of the row to orange. it worked fine but now I have more than 3 conditions so I needed some code to allow for 4 or 5 conditions.

I found this code and adapted it for my spreadsheet but it just won't work! I have also disabled all the conditional formatting that I had in each sheet. I tried putting the code in the "workbook" and no luck so I have the code in every sheet but it still won't work. ANY help would be really appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Set VacancyListOfferWoods = Range("A2:AY300")

For Each Cell In VacancyListOfferWoods

Select Case Cell.Value

Case Is = "C"
Cell.EntireRow.Interior.ColorIndex = 7

Case Is = "F"
Cell.EntireRow.Interior.ColorIndex = 8

Case Is = "DB"
Cell.EntireRow.Interior.ColorIndex = 4

Case Is = "Q"
Cell.EntireRow.Interior.ColorIndex = 3

Case Else
Cell.EntireRow.Interior.ColorIndex = xlNone

End Select

Next
End Sub

**Richard Buttrey** · 11-02-2011, 08:10 PM

Hi,

Use the following Workbook_SheetChange event - note Workbook sheet change not Worksheet change

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Range("A2:Ay300")) Is Nothing Then

        Select Case Target.Value
        Case Is = "C"
            Target.EntireRow.Interior.ColorIndex = 7
        Case Is = "F"
            Target.EntireRow.Interior.ColorIndex = 8
        Case Is = "DB"
            Target.EntireRow.Interior.ColorIndex = 4
        Case Is = "Q"
            Target.EntireRow.Interior.ColorIndex = 3
        Case Else
            Target.EntireRow.Interior.ColorIndex = xlNone
        End Select

    End If

End Sub

Regards

**royUK** · 11-03-2011, 03:14 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here