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

 

[alansidman]

Here is a VBA solution for you.

Option Explicit

Sub Garden()
    Dim c As Range, rng As Range
    Set rng = Range("A1:Y25")    'change this to reflect your range
    For Each c In rng
        If c = "Garden" Then    'change this to your lookup word
            c.Interior.ColorIndex = 6  'change this to the color you wish.
        End If
    Next c
End Sub

 

[Rick Rothstein]

Uusing Alan's assumed range and font color (change them to suit your needs), here is another macro that you can consider...

Sub Garden()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 6
  Range("A1:Y25").Replace "Garden", "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

 

[Peter_SSs]

here is another macro that you can consider...

Don't think Find/Replace format was available in Excel 2002 (XP). Not until Excel 2003 I think.

 

[Rick Rothstein]

Don't think Find/Replace format was available in Excel 2002 (XP). Not until Excel 2003 I think.

I never had XL2002, so I don't know (XL2003 was the earliest version I ever had). If you are right, then the OP will know why my code fails should he decide to try it.

 

[anon125]

thanks guys
how many conditional formattings can be done?

 

[Fluff]

Xl 2003 had a limit of 3 CF rules, if I remember rightly. So I suspect it will be the same in 2002

 

[anon125]

UH OH!
Thanks

 

[Fluff]

You're welcome

 

[Peter_SSs]

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.

Will the cells with the "other word" have the same font colour as 'Garden' or a different font colour for each word?

If all the same colour then you should be able to use a Conditional Formatting formula like this. Here I want to format 'Garden', 'Gate' and 'Shed' but not other words.
(Due to a glitch with XL2BB I have formatted cell colour instead of Font colour but Font should work on your sheet)

Book1
	A	B	C	D	E	F
1				Shed
2	Garden			Shed
3	Path		Garden			House
4			Garden			House
5		House	Garden	Garden	Garden
6			Garden	Garden
7				Garden
8		Shed				Gate
9			Shed
CF Font

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:F9	Expression	=SEARCH("|"&A1&"|","|Garden|Gate|Shed|")	text	NO