23

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.

  • If you dont want to do it manually, how do you want it decided what color things should be? – soandos Jul 27 '11 at 18:14
  • 2
    I would like it automatic if possible, similar to how colors are chosen for different series in a chart. – Steven Jul 27 '11 at 18:27
  • Ah, so you want all cell with the same contents to be the same color, but dont care which color it is? – soandos Jul 27 '11 at 18:39
  • Would a one-time macro (to create the conditional formats once) be acceptable? It would need to be run exactly once per workbook and could be removed after that. – Tex Hex Jul 27 '11 at 19:58
  • soandos: Yes, TeX Hex: Sure! – Steven Jul 27 '11 at 20:14
6
  1. Copy the column you want to format to an empty worksheet.
  2. Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon.
  3. 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.
  4. In a new column, use VLOOKUP to map the text string to the new color.
  5. Apply conditional formatting based on the new numeric column.
| improve this answer | |
  • 3
    Step 4 is a bit unclear to me, could you please elaborate? Thanks. – pixels Sep 23 '15 at 20:38
  • 1
    Could you elaborate on 5? – zthomas.nc Mar 26 '17 at 17:37
  • 1
    But surely this then means the formatting is on the cells containing the numeric value and NOT the text value – adolf garlic Feb 15 '18 at 10:39
11

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.

enter image description here
(Click image to enlarge)

Now Just select whatever formatting you want.

enter image description here

| improve this answer | |
  • 5
    Isn't this still going to require that the OP manually create a conditional format for each value? – Dave DuPlantis Jul 27 '11 at 20:12
  • 1
    @Dave DuPlantis - NO. You can select ALL cells then use the conditional formatting. All cells that met the condition will be formatted accordingly. – Nicu Zecheru Jul 28 '11 at 11:06
  • 7
    Each condition still has to be created manually, even though they only need to be created a single time for the entire workbook. He's looking for a solution that doesn't require him to specify the values. – Dave DuPlantis Jul 28 '11 at 11:24
  • So is it possible to have multiple rules for the 'text contains'? this is still pretty poor functionality from ms – adolf garlic Feb 15 '18 at 10:37
2

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
| improve this answer | |
  • I see that I already upvoted this answer, but I can't find whatever code I ended up using. One day I'll eventually write some flexible code and share it here too. – Ryan Nov 21 '19 at 19:49
1

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.

  1. Create a New Formatting Rule in Conditional Formatting.
  2. Use a formula to determine which cells to format.
  3. Formula: =$B1="bedroom" (Assuming the category column is B)
  4. Set Format (using Fill color)
  5. Apply rule formatting to all cells
| improve this answer | |
  • 2
    Fyi, Eric has posted a much more useful answer... yours instead looks like a rehash of the first answer you got. – Frank Jul 31 '15 at 14:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.