Questions

Color coding a drop-down list in Excel's data validation function

+
0 Votes
Locked

Color coding a drop-down list in Excel's data validation function

aschonfeldt
Hi

I need to know how to color code a list built in Excel's data validation function depending on the response selected

Hellppppppp!
+
3 Votes
bazmorrison
Collapse -

1) Create your drop down list in any cell using Data Validation with fields. e.g, Low, Medium,High
2) Highlight the drop down cell.
3) Select Conditional Formatting
4) Select Highlight cell rules, more rules
5) Select Format only cells that contain
6) Change the value in the format only cells with: to Specific Text
7) Enter the text field . e.g. Low
Select Format tab.
9) Select the Fill tab and select your colour e.g. Green.
10) Click Ok twice.
11) Repeat steps 2 to 10 for each other drop down selection e,g, Medium for amber, and High for Red.
12) Test the colours work if not you've done somethign wrong above.
13)After all this just cut and paste the finished drop down to everywhere you want to use it.

Hope this helps it drove me mad for about 10 minutes.

+
0 Votes
pscharf
Collapse -

Conditional formatting only allows three formatting rules per cell, os if you list is longer than 3 items it does not work. I'd love to find a way to do this.

+
0 Votes
m.suman3ikp
Collapse -

it is working
Thank you so much man

+
0 Votes
Sederu
Collapse -

I'm running Excel 2010. I went through your list and It worked as long as I kept the information I drew from to create the list. Is there a way to create a list and then delete the extra information that was only needed to create the list? Or can I hide it, I want it to look professional if possible.

+
0 Votes
chandu83
Collapse -

It is definitely possible to use the method described above for more than 3 cells.
Check this link
http://clip2net.com/clip/m79515/1307259484-clip-8kb.png
It is possible to define conditional formatting for more than 3 cells as you can see in the picture. I defined the color formatting using the method described above.

+
0 Votes
Mass14
Collapse -

I do not have the more rule function, i'm using excel 2007. Also, what if i had row that went from A to J and when I enter "DONE" in J, all of the boxes turned red. Can this be done?

+
0 Votes
kanikohli
Collapse -

Great Info!! very helpful.

Thanks.