up vote 2 down vote favorite
1
share [g+] share [fb]

An Excel column contains a text value representing the category of that row.

Is there a way to format all the 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.

link|improve this question
86% accept rate
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
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
feedback

1 Answer

up vote 0 down vote

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

Now Just select whatever formatting you want.

enter image description here

link|improve this answer
1  
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
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
feedback

Your Answer

 
or
required, but never shown
discard

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