[Solved] Highlight if item chosen via pick list from named range

[PeterBoots]

Hi,
I've setup a simple spreadsheet to organise volunteers when they are on duty.
I've created a named range which contains all the volunteers.
I've created a picklist to select the volunteers from the named range.
I'd like to be able highlight in a colour if the volunteer has been selected from the named range, so I can see who I may have omitted to select from the named range.
So in this case, I'd like to see Volunteer3 highlighted in a different background or text colour to show that I haven't picked them

Volunteer ROSTER
Day	Date	Help
WEDNESDAY	3-May	Volunteer1
THURSDAY	4-May	Volunteer2
WEDNESDAY	10-May	Volunteer2
Volunteer1
Volunteer2
Volunteer3

 

[Bishawajit]

Hi,
I've setup a simple spreadsheet to organise volunteers when they are on duty.
I've created a named range which contains all the volunteers.
I've created a picklist to select the volunteers from the named range.
I'd like to be able highlight in a colour if the volunteer has been selected from the named range, so I can see who I may have omitted to select from the named range.
So in this case, I'd like to see Volunteer3 highlighted in a different background or text colour to show that I haven't picked them

Volunteer ROSTER
Day	Date	Help
WEDNESDAY	3-May	Volunteer1
THURSDAY	4-May	Volunteer2
WEDNESDAY	10-May	Volunteer2
Volunteer1
Volunteer2
Volunteer3

Dear Peterboots,
Here is the solution to your problem. Please follow the below steps accordingly.

• Firstly, select cell range E3:E5.
• Then, go to the Name box and type “Volunteers”.
• Next, press Enter.

• Now, select cell range B3:B8.
• After that, go to the Home tab >> click on Conditional Formatting >> select New Rules.

• Then, insert the following formula in the New Formatting Rule box.

=COUNTIF(Volunteers,B3)

• Afterward, click on Format to set the background color.

• Finally, you can see the highlighted no selected volunteers here in the below image using conditional formatting in Excel.

I hope, you will get your solution here.

 

[AlphonsoEstrada]

How happy I am when I find the question and the answer I needed recently)

 

[shamimarita]

How happy I am when I find the question and the answer I needed recently)

We are glad to hear that.