Conditional Formatting- Drop-Down List Reference for single cell

[LaurenCely]

I'm having trouble formatting a single cell. The cell has an index match formula in it based on a drop down list:

In cell F1 --> I want it to show RED for Janet but GREEN for Ben or Adam, depending on which name I pick in the drop-down list (cell E1 is a drop down list).

Right now, I have an Index Match Formula in the cell, and the conditional formatting has to match whichever name is chosen....Can this be done?

	A	B	C	D	E	F
1	ID	Test1	Test2	Color	Janet	67
2	Ben	89	84	Green
3	Adam	92	96	Green
4	Janet	56	67	Red

<tbody>
</tbody>

 

[MARZIOTULLIO]

Hi

Conditional formatting

Use a formula to determine which cells format

=OR($E$1=$A2,$E$1=$A3) Green Applies to =$F$1

=$E$1=$A4 red Applies to =$F$1

 

[SpreedsheetCrusader]

Hi bro

just next to the selector create a helper cell with a Boolean condition based on your preference then set a conditional formatting on the score based on your thresholds

	C	D	E	F	G	H	I	J	K	L
1
2
3		id	result			Selector			Helper
4		Ben	84			Janey	67		1
5		Adam	96
6		Janey	67
7
8
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula I4 =INDEX($D$4:$E$6,MATCH(H4,$D$4:$D$6,0),2) K4 =--(I4<70) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[LaurenCely]

Hi

Conditional formatting

Use a formula to determine which cells format

=OR($E$1=$A2,$E$1=$A3) Green Applies to =$F$1

=$E$1=$A4 red Applies to =$F$1

The table I gave is just an example-- my actual Data Table is A1:AC119. I could have the cell = a range, but it would be tedious to select all the cells with just "Green", and also be inaccurate if that should change later to a "Red".

I think the syntax needs to be something along the lines of :

If cells range A1:A119="Green" (in any cell) for the TABLE A1:AC119,THEN color cell $F$1 Green. OTHERWISE, color cell $F$1 Red.