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

PeterBoots

New member
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
DayDateHelp
WEDNESDAY3-MayVolunteer1
THURSDAY4-MayVolunteer2
WEDNESDAY10-MayVolunteer2
Volunteer1
Volunteer2
Volunteer3
 
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
DayDateHelp
WEDNESDAY3-MayVolunteer1
THURSDAY4-MayVolunteer2
WEDNESDAY10-MayVolunteer2
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.
SS_1.png
  • Now, select cell range B3:B8.
  • After that, go to the Home tab >> click on Conditional Formatting >> select New Rules.
SS_2.png
  • Then, insert the following formula in the New Formatting Rule box.
=COUNTIF(Volunteers,B3)
  • Afterward, click on Format to set the background color.
SS_3.png
  • Finally, you can see the highlighted no selected volunteers here in the below image using conditional formatting in Excel.
SS_4.png
I hope, you will get your solution here.
 

Online statistics

Members online
0
Guests online
7
Total visitors
7

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top