Color Cell Based On Text Value
The screenshots below are from Excel 2010, but should be the same for 2007.
Select the cell and go to Conditional Formatting | Highlight Cells Rules | Text that Contains
UPDATE: To apply the conditional formatting for the entire worksheet select all cells then apply the Conditional Formatting.
(Click image to enlarge)
Now Just select whatever formatting you want.
- Copy the column you want to format to an empty worksheet.
- Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon.
- To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your lookup table.
- In a new column, use
VLOOKUP
to map the text string to the new color. - Apply conditional formatting based on the new numeric column.
From: http://www.mrexcel.com/forum/excel-questions/861678-highlighting-rows-random-colors-if-there-duplicates-one-column.html#post4185738
Sub ColourDuplicates()
Dim Rng As Range
Dim Cel As Range
Dim Cel2 As Range
Dim Colour As Long
Set Rng = Worksheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Rng.Interior.ColorIndex = xlNone
Colour = 6
For Each Cel In Rng
If WorksheetFunction.CountIf(Rng, Cel) > 1 And Cel.Interior.ColorIndex = xlNone Then
Set Cel2 = Rng.Find(Cel.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
If Not Cel2 Is Nothing Then
Firstaddress = Cel2.Address
Do
Cel.Interior.ColorIndex = Colour
Cel2.Interior.ColorIndex = Colour
Set Cel2 = Rng.FindNext(Cel2)
Loop While Firstaddress <> Cel2.Address
End If
Colour = Colour + 1
End If
Next
End Sub