Conditional Formatting multiple cells if one cells says "X"...

[Guest]

Hi All
I know that I need to write code (VBA) to address this conditional
formatting, but I'm a novice and after reading all the postings on
conditional formatting I can't work it out - please help...

There are 5 conditions and the formatting applies to a growing number of
cells (current range is M14:GR603 - but the number of rows will increase).

The conditions are:
If "Retail" is entered in column E of this row, then colour this cell pink
If "Brand" is entered in column E of this row, then colour this cell blue
If "Special" is entered in column E of this row, then colour this cell yellow
If "Generic" is entered in column E of this row, then colour this cell green
If "Other" is entered in column E of this row, then don't colour the cell.

I am using Excel 2000 (if that makes any difference).
Thank for your help
BeSmart

 

[Bernie Deitrick]

BeSmart,

You can use the worksheet's change event keyed to column E.

Copy the code below, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("E:E")) Is Nothing Then
For Each myCell In Intersect(Target, Range("E:E"))
If myCell.Value = "Retail" Then
Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _
.Interior.ColorIndex = 38
End If
If myCell.Value = "Brand" Then
Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _
.Interior.ColorIndex = 37
End If
If myCell.Value = "Special" Then
Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _
.Interior.ColorIndex = 36
End If
If myCell.Value = "Generic" Then
Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _
.Interior.ColorIndex = 35
End If
If myCell.Value = "Other" Then
Range(Range("M" & myCell.Row), Range("AG" & myCell.Row)) _
.Interior.ColorIndex = xlNone
End If
Next myCell
End If
End Sub