How to highlight the cell by conditional formatting by comparing two columns ignoring BLANK match?

1
votes

I am trying to highlight the cell by conditional formatting by comparing two columns B and C

The condition is like to ignore BLANKS even if both columns have blanks on same row ,only to highlight the matching values So I wrote

=AND(B2=C2,C2<>"")

Please note the empy cells are not empty . If we click on empty cell , there's a hidden IF formula .But the value of cell is blank.

Its not working . Pls help

2
excelexcel-formulaexcel-2010excel-2007
Works for me, what exactly is the issue? Are you getting some conditional formats displaying where you don't expect them? None displaying at all? Please explain the issue a bit more and I should be able to help... Thank you. - Glitch_Doctor
There are so many potential causes, a more detailed explanation of what you are experiencing will help me diagnose the cause and be able to offer the solution, please consider what I have mentioned in the above comment and explain the output you get when using the conditional format formula. - Glitch_Doctor
Compare your formatting rule with my flie: drive.google.com/open?id=0B2x_I7I1nAg1akxqYlhRbmpoX0U - bzimor
Can I have the IF formula that's in column C please? Might be able to tweak a workaround and I will have the correct formula to play with on my sheet. Thanks. - Glitch_Doctor
@bzimor Logically your sheet is correct and works good in your case but the same formula is not working in my datasheet. I guess for blank "" we need to give some other syntax and the cell value is blank but has IF formula when we click on that cell . May be because of that its not reading the formula - user41048

2 Answers

1
votes

Solution

=AND($B2<>"",$C2<>"",$B2=$C2)

Follow the steps in the below GIF to see how to use it - Conditional Formatting GIF

The logic

Fixate only the columns B and C, and make sure that you apply the rule on the first cells of the selected range, in this case it's row number 2. Excel will look interpret the formula this way -

  1. Condition - Apply the fill in color only if all of the rules apply in the AND function
  2. $B2<>"" - checking that the first cell in the selected range is not blank.
  3. $C2<>"" - checking that the first cell in the selected range is not blank.
  4. $B2=$C2 - checking that the first two cells in the selected range are equal.
  5. Apply the same rule for the rest of the selected range according to given fixation - columns B and C.

According to 5 - Excel loops over your selected range like this - 

=AND($B3<>"",$C3<>"",$B3=$C3)
=AND($B4<>"",$C4<>"",$B4=$C4)
...
=AND($B9<>"",$C9<>"",$B9=$C9)

That's an awesome magic trick in Excel's conditional formatting.

For practice purposes, I suggest you try highlighting the whole line according to the same condition. And I also suggest you try to fixate $B$2 and $C$2 - Excel will interpret your formula only on these cells because you fixated it on them, that's a common mistake so watch out.

I hope it helps

0
votes

Use this formula in conditional formatting rule:

=IF(AND(B2=C2,C2<>""),TRUE)