find and then change font colour excel 2002

[anon125]

we have a grid of 25 by 25 cells
the cells have words in them.
I can find say 'garden' and find them all.
how do i change the font colour for all of the garden ones?

and the same for other cells with another word in them.

is it simple? for me i sure hope so!

thanks all

 

[anon125]

different font colour for each - as many as possible!
thanks

 

[Peter_SSs]

different font colour for each - as many as possible!
thanks

Then you won't be able to use Conditional Formatting as your Excel version only allows 3 different formats with CF.
Here is an extension of the idea given by Alan in post 2. Your range of 25 x 25 is not very big but if you were to apply this to a very large range, a different approach might be warranted, depending on how many of each value you might expect to find in that range.

Sub Colour_Values()
  Dim c As Range
  Dim Vals_To_Colour As Variant, Colour_Vals As Variant
  Dim i As Long
 
  Vals_To_Colour = Split(LCase("Garden|Gate|Shed"), "|")
  Colour_Vals = Split("3|5|10", "|")
  If UBound(Vals_To_Colour) = UBound(Colour_Vals) Then
    Application.ScreenUpdating = False
    For i = 0 To UBound(Vals_To_Colour)
      For Each c In Range("A1:Y25")
        If LCase(c.Value) = Vals_To_Colour(i) Then c.Font.ColorIndex = Colour_Vals(i)
      Next c
    Next i
    Application.ScreenUpdating = True
  Else
    MsgBox "Number of values and colours do not match"
  End If
End Sub

 

[anon125]

Wow Thanks!

 

[Peter_SSs]

Wow Thanks!

You're welcome.

 

[anon125]

where do i find out which numbers mean which colours?
thanks

 

[Fluff]

Have a look here

Color Palette and the 56 Excel ColorIndex Colors

Excel Color Index, coloring of fonts, cell interiors

dmcritchie.mvps.org

 

[anon125]

UH OH it aint standard?
thanks for your help

 

[Peter_SSs]

UH OH it aint standard?

What do you mean?

 

[anon125]

i (mis) understood the link page to say the colours and numbers are not all the same from version to version.
thanks

 

[Peter_SSs]

Oh, okay. Is that a problem, as the words would still show up as different colours?

If it is then you could do some research about copying the color palette from one workbook to another (mentioned in that same article) or perhaps investigate using

.Font.Color (& use RGB settings for the various colours that you want)
instead of
.Font.ColorIndex

Working with color palettes and color generally is not a strength of mine though.