Hi
I need to know how to color code a list built in Excel's data validation function depending on the response selected
Hellppppppp!
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Color coding a drop-down list in Excel's data validation function
31st Jan 2008
Answers (4)
2
Votes
This is how you do it
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
8) 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.
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
8) 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.
8th Feb 2008
Replies
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.
pscharf@...
22nd Dec 2008
it is working
Thank you so much man
Thank you so much man
m.suman3ikp
17th Jul
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.
Sederu
15th Jan
0
Votes
Possible to use with more than 3 cells
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.
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.
5th Jun 2011
0
Votes
Color coding a drop-down list in Excel's data validation function
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?
17th Jun 2011
0
Votes
Color coding.
Great Info!! very helpful.
Thanks.
Thanks.
21st Nov 2011
