RAG dropdown with color conditional formatting

bowemc
bowemc used Ask the Experts™
on
Hi,

I'm creating a RAG report in excel. I need to have a drop down box in each cell of column E with the options - Amber, Complete, Green, Red. I want to have conditional formatting so then when a user selects one of the options it sets the cells background color to that of the selected option - i.e Green=Green etc. (Complete can be purple.

Also if you've done this before have you any nice shades of the colors - notging worse than shades that burn your retna!!

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
bowemc,

Have a look at the Format/Cells for column E as well as the Format/ConditionalFormatting for column E. It's in the attached file.

Patrick
Cond-cols-01.xls
ps. You can of course use more attractive colours by changing the colour palette, but I would not recommend it if your RAG report is going to be viewed on other pcs as their colour palettes won't coincide with your set up and it will look and be a colour mess. So best stick with the retina-burning colours - disappointing though they are!

Patrick

Commented:
I've attached an implementation of what you want in the attached sheet.  I've created in cell validation for cells E1 to E20, which use the list on the same sheet.

Conditional formatting is set up for each of the possibilities.  Using this it should be pretty easy to apply to your own work.

HTH,
Matt
Conditional-formatting-and-valid.xls
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Commented:
Oops.  didn't pay enough attention to the brief :(
If you really want to mess around with the colour palette (Tools/Options/Color) then you can produce the sort of colour options shown in the attached file - but I don't recommend it.

Patrick
Cond-cols-02.xls
>Oops.  didn't pay enough attention to the brief :(

You or me?

Patrick

Commented:
Me..  I didn't include a 'Complete' option..
>Me..  I didn't include a 'Complete' option..

Yes you did, you said it should be purple, which is exactly what I implemented as the default background colour. Purple over-ridden if Amber, Green or Red is selected. Please see my file given above.

Patrick

Author

Commented:
Hi patrick, couldn't i do it through vba and set the color there in a hex format so it's portable? I was think i need some way to check the active cell (in col E) if value is changed?

Thanks
bowemc,

Perhaps you should have specified what you wanted in the first place rather than after you have been provided with solutions.

I suggest you close this question and open a new one.

Patrick

Author

Commented:
I'm happy to do that, but I thought I out lined the requirements at the start. I stated I wanted custom colors and this I thought would lead to an immediate vba solution
bowemc,

No you did not specify VBA, instead you specified dropdown boxes and Conditional Formatting - both of which were supplied in the solution. Custom colours I have delivered although I have advised against using them - and I have explained why.

If you want a VBA solution right from the start, then you should be specifying that in your question. Apart from that it is more normal to make the cells change colour automatically depending on their value - however you did not specify that either.

Patrick
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you set up a custom palette, that will travel with the workbook, so just modify the colours as required.
bowemc - Thanks for the grade - Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial