An Excel column contains a text value representing the category of that row.
Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format for each value?
Example: If I had the categories
bedroom, bedroom, bathroom, kitchen, living room, I would want all cells containing
bedroom to be a particular color,
bathroom a different color, etc.
- 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
VLOOKUPto map the text string to the new color.
- Apply conditional formatting based on the new numeric column.
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.
Now Just select whatever formatting you want.
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
The automatic color choosing Conditional Formatting is not a feature of Microsoft Excel.
However, you can color an entire row based on the value of a category column individually.
- Create a New Formatting Rule in Conditional Formatting.
- Use a formula to determine which cells to format.
=$B1="bedroom"(Assuming the category column is B)
- Set Format (using Fill color)
- Apply rule formatting to all cells