Conditional Format Question

[Craigc3814]

I have text returned from a formula in cell B27, I want conditional formatting to look at the value in B27, find that value in cells H21:H25, then highlight the cell directly next to it.

For example
I have the formula in cell B27 is returning the word Needs Imprv (It changes as new data comes in).
I want to then look at the scale in cells H21:H25 (This scale never changes it goes from Exceptional to Fails)
When it finds Needs Imprv. which is in cell H24 I want it to highlight I24

 

[Joe4]

Select cells I21:I25 (those are the cells that you want to apply the Conditional Formatting to) and enter in the following Conditional Formatting Formula:

=$H21=$B$27

and choose your highlighting option.

 

[Craigc3814]

but the value in B27 can be in H21 - H25

 

[Joe4]

but the value in B27 can be in H21 - H25

I know, but you want to highlight column I, not column H, right?
If you do exactly as I tell you, it will work.
Try it and see!

 

[Craigc3814]

I was not catching on that the you were not locking down all of H21 just the column. That worked thank you.

 

[Joe4]

You are welcome.
Yep, the placement of the Absolute Range References is critical.

 

[Craigc3814]

I cant believe the formula was that easy. I was trying to do a vlookup or index match.