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?

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Test1
[/TD]
[TD]Test2
[/TD]
[TD]Color
[/TD]
[TD]Janet
[/TD]
[TD]67
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Ben
[/TD]
[TD]89
[/TD]
[TD]84
[/TD]
[TD]Green
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Adam
[/TD]
[TD]92
[/TD]
[TD]96
[/TD]
[TD]Green
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Janet
[/TD]
[TD]56
[/TD]
[TD]67
[/TD]
[TD]Red
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
id	result	Selector	Helper
Ben	Janey
Adam
Janey

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]84[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=INDEX($D$4:$E$6,MATCH(H4,$D$4:$D$6,0),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K4[/TH]
[TD="align: left"]=--(I4<70)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.