Can a dropdown selection format specific cells in a list?

Hoping this be done without going to VBA, but open to suggestions...

Suppose I have a list of 5 vegetables and depending on who I am shopping for, I need to buy a different subset of those vegetables. Is it possible to put the names in a dropdown selector, and have the list of fruits called-out for that name? While also leaving the non-desired items un-highlighted? Example table below and screenshot of what i want to have happen. Is this possible? Are there other non-VBA options i'm not considering (e.g. checkboxes get auto-checked?). Thanks for any ideas!

NAME	APPLE	ORANGE	BANANA	PEAR	GRAPES
Paul	X	X	X
John	X	X
Ringo		X		X	X

Capture.PNG

Thanks Tony. This appears to definitely work for half my problem - but is there a way to have the array also show the items that are not listed (formatted differently)? I'm looking to have all Fruits in the list, but then only format the desired ones based on the name selected.

1 validation list, 5 coditional formatting


Kind regards
Leo

@Tony, i can't seem to get that to work. it still only shows the populate values in the array list.

@Leo, i think i have reverse engineered your solution to see how it was done - really nice! But i'm having issues converting it to my purpose. It turns out the example I gave has the columns and rows switched, so i updated my example below to reflect this. How can you get the conditional formatting formula of =LEN(INDIRECT("B"&MATCH($H$1,$A:$A,0)))=0 to now find the right cell to look for the 0 value?

FRUIT	Paul	John	Ringo
Apple	X	X
ORANGE	X	X	X
BANANA	X
PEAR			X
GRAPES			X

See second sheet (blad2)

beside the validation list and the conditional formating
there is also an named range ( for the names )


Cheers
Leo

Using conditional formatting, Iam using Leo Taxi file's

@ azumi

cost you 1 beer

Thanks Tony, i understand now. Your solution looks to be pretty simply now that i see it's removed the array component. To help, here is a the sample file i am trying to create. I want A2:A15 to format based on what is selected in A1. In columns C, D, and E you can see what my desired result is. I also included a grid for indexing in columns G through J. Thanks again for the help, definitely appreciate it! I was not familiar with conditional formatting via formulas, so this looks to be a good win.

Hope this works

Same result,

formula in conditional formatting shorter



Kind regards
Leo

Thank you all, these examples are perfect and I have what I need. Conditional Formatting formulas ... a basic idea, but one I never realized the power of.

Formula from #13

=LEN(INDEX($G:$I,ROW(),MATCH(A$1,$G$1:$I$1,0)))>0


Cheers
Leo