Conditional formatting - highlight cells based on text in another

[BravoBravoAu]

Hi guys - hopefully another easy Conditional Formatting issue.

In my workbook, if cell J2 contains YES, I want E2 and F2 to be highlighted Green and so on throughout the sheet on a row by row basis.

I have a rule in conditional formatting corresponding to E2 and F2.

• Formula: = $J2="YES".
• Format: fill, green.
• Applies to: $E$2:$F$10896

I can get row 2 to 'work' using the above, but when I type YES into J3...nothing happens.

Help please?

 

[Joe4]

It works fine for me. Perhaps you have other rules interfering with it?

Try the following to reset the whole thing and start over:
- First, remove ALL conditional formatting rules from your sheet
- Then select the range E2:F10896
- Then go to Conditional Formatting, go to the Formula option and enter the formula: =$J2="YES"
- Select your green fill option
- Click OK

This should do what you want.

 

[BravoBravoAu]

Thanks @Joe4.

I followed your suggestion (acknowledging it was as I had previously), and no improvement. In fact, now, not even row 2 is highlighting... I cleared all conditional formatting in the workbook prior.

The YES result in column J is derived from a droplist (YES/NO), but I can't see that this would be a problem.

 

[Joe4]

There is really only two things I can think of that would cause it not to work:

- Your drop list isn't really returning "YES". Maybe there is an extra space on the end that needs to be removed.
Here is a simple test. Pick any cell that is equal to "YES" from this drop-down. Let's say it is cell J2. Then enter this formula in any blank cell:

=J2="YES"

If that does not return TRUE, then there is more to your drop-down then "YES", probably an extra space somewhere.
You can check that with this:

=LEN(J2)

which should return 3.

If the formula =J2="YES" returns TRUE, and your Conditional Formatting still isn't working, then it is most likely that you have not set it up right. Make sure that you are selecting JUST the rows you want to apply it when entering the formula, and NOT the whole column.

Also, which version of Excel are you using, and do you have any merged cells anywhere on the sheet?

 

[BravoBravoAu]

@Joe4 - you are a genius!!! I hadn't considered that was the issue and was remiss to not mention it in my initial post.

Both of your troubleshooting steps confirm that was the issue and rectifying the extra space at the end of the 'YES' in the drop list has cleared the problem.

Thank you so much.

 

[Joe4]

You are welcome!

Glad we got it all sorted.