eve

This macro will do the trick.

Just add more nums and vals to cover etc., etc.

Sub foo()
Set r = Range("A1:M300")

vals = Array("A", "B", "C", "D", "E", "F", "G")
nums = Array(8, 9, 6, 3, 7, 4, 20) ' colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

To get a list of numbers for the color palette see David McRitchie's site or go
into the VBE and VBA help under "PatternColorIndex Property".


Gord

On Thu, 3 May 2007 23:19:01 -0700, eve wrote:

thank you for that but what I meant was that I want all the cells with A in
say in red, all the cells with B in say in blue etc etc. The spread sheet
is quite big so doing it individually will be a nightmare.

"eve" wrote:

I want to shade all the identical cells in a large spread sheet to pick out a
pattern How can I do this without having to do it all individually. Many
thanks for any help.