Color coded dropdown lists

[charlemjamSage]

I'm using Microsoft Excel for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20226) 64-bit

I have a status report for which I'd like to use a dropdown list for different statuses and have the associated colour in the cell where the status is selected. 

I am able to create the dropdown list and use it, just the colour coding doesn't come across with it.

I have a file of the status report but can't see an option to attach it...

Thanks

[#2]

@charlemjamSage 

Select the cell or cells with the data validation drop-down.

Create a conditional formatting rule for each value of the drop-down list.

[#3]

Thanks @HansVogelaar for the quick response. I don't use Excel very often, how do I do the create part you've mentioned please? Feel free to point me to existing instructions!

[#4 · Accepted solution]

@charlemjamSage 

Let's say your drop-down list has options One, Two and Three, and you want to color the cell red, yellow or green depending on the selected item.

Select the cell or cells with the drop-down.

On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
In the box next to it, enter the formula

="One"

Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.

Repeat these steps, but with ="Two" as formula and yellow as fill color.

Repeat them again, with ="Three" as formula and green as fill color.

[#5]

@HansVogelaar Thanks - I followed the instructions and get an error message saying "Enter a valid formula" - see photo

[#6]

@charlemjamSage 

You should use double quotes instead of single quotes:

="Not started"

[#7]

Thanks so much - it works!! Really appreciate your clarity and patience 🙂 (why is there a limit on how many messages can be posted in 14,400 seconds??? Where is that number plucked from?!) Is there another place for unlimited instant messaging where you can also get help/advice? I was unable to post this yesterday as a result, sorry

[#8]

@charlemjamSage 

Most forums have a limit on how frequently you can post, to deter spammers. But 14,400 seconds is 4 hours; I have no idea why it is so long.

I'm one of the administrators of Eileen's Lounge. The minimum interval between posts there is 1 minute.

[#9]

@charlemjamSage , @HansVogelaar 

That's really strange. Did you see what is the actual limit, 1 reply, 10 replies, etc.? Based on our experience here replies could go one by one.

[#10]

Yes, it said I'd reached the maximum 8 posts allowed in 14,400 seconds

[#11]

@charlemjamSage 

I see, thank you. Most probably that depends on user profile, one limit for Occasional Visitor and other limits for higher ranks. have no answer, you may clarify that on https://techcommunity.microsoft.com/t5/community-lounge/ct-p/Community-Info-Center.

Anyway, from my point of view 8 message in 4 hours is quite strong.

[#12]

@HansVogelaar Thank you for sharing this response to the original question regarding Excel and coloured drop down lists. 

[#13]

for me it doesnt work...care to help?

[#14]

@htews 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#15]

@HansVogelaar 

it doesnt want to carry over to "7 Oct" sheet/tab

[#16]

@htews 

I'm afraid a screenshot doesn't help. See my previous reply.

[#17]

in the 'project list' is the list of the projects that i have worked on.
'7 oct 24' is the timesheet currently.
all are within a single file.
not sure if i did this correctly...i shared the file.

[#18]

@htews 

I do not see a link to a file

[#19]

i think i sent it via private message?

[#20]

@htews 

Thanks. Since you want to highlight cells on the 7 Oct 24 sheet, you should create the rule(s) on that sheet:

• Activate the 7 Oct 24 sheet.
• Select A5.
• On the Home tab of the ribbon, select Conditional Formatting > New Rule...
• Select 'Format only cells that contain'.
• Leave the first drop-down set to 'Cell Value'.
• Select 'equal to' from the second drop-down.
• Type = in the box next to it, then point to cell A30 on the PROJECT LIST sheet. You should see ='PROJECT LIST'!$A$30
• Click Format...
• Activate the Fill tab.
• Select a color.
• Click OK.
• If necessary, repeat for other project names, with different colors.
• With A5 on the 7 Oct 24 sheet still selected, double-click the Format Painter button on the Home tab of the ribbon.
• Click on A6:A16, then on A18:A32, then on A34:A45, etc. to copy the formatting.
• Press Esc when you're done to turn off the Format Painter.