MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Colouring cells

Posted by Jean Trenell on November 18, 2001 12:11 PM

Has anyone any ideas as to how I can automatically colour cells which contain one particular word, one colour (say, yellow), and other cells containing a different word another colour (eg green) – and the same with other cells containing different text .. and so on. As there are more than three sets of words conditional formatting will not work. Any ideas would be much appreciated.
Many thanks

Posted by Gary Hewitt-Long on November 18, 2001 3:55 PM

Try this:
You will need to add more of these to your procedure depending on how many different words you want to look for.
This one looks for "SHARED" in the Range C2:C2000 and then colours the cells a Jade colour.

With Worksheets(1).Range("C2:C2000")
Set c = .Find("SHARED", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
c.Interior.Interior.ColorIndex = 42
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Gary Hewitt-Long

Posted by noir on November 19, 2001 5:35 AM

:Gary,i am just starting out and don't understand how to enter the commands you posted. I am completely familiar with typing a (=) before entering a formula but, need help understanding your instructions.