Conditional Formatting

[Peter Atherton]

Gareth

This should do the trick, change the color index to suit.

Sub Frmat()
Dim list As Range, crit As Range
Dim c
Set list = Range("A3:A101")
Set crit = Range("A2")
For Each c In list
If c.Value = crit Then
c.Interior.ColorIndex = 17
End If
Next c
End Sub

Regards
Peter

 

[David McRitchie]

I think you want to set the color to a default color if not set to colorindex
of 17; otherwise, all cells that can get set would probably eventually
be set to 17 from previous or current assignments.

Another method is an Change Event macro, an example in
http://www.mvps.org/dmcritchie/excel/event.htm#case
a bit of overkill, but then I think you would have done fine with
conditional formatting. Conditional Formatting has the advantage
of not caring how the values in the cell changes whether manually,
by formula, or by pasting in but does have the limitation of 3
sets of C.F per cell.