Thanks:  0
Likes:  0

1. ## Conditional Formatting Question

I need some help with conditional formatting. I have three columns (C2:E31) with data in them. Column B also has data in it. When the value in column B is changed and the values of Columns C, D and E do not match what B is changed to, I want C, D and E to change color. Can someone please assist?

2. ## Re: Conditional Formatting Question

Try

Select C2:E31
In CF use the formula option

Insert this formula
=AND(\$B2<>"",COUNTIF(\$C2:\$E2,\$B2)=0)

Format button and pick the format you want

Hope this helps

M.

3. ## Re: Conditional Formatting Question

Thanks for the response. That didn't quite work.

For example, in one row, Column B (the control column) has A in it, column C has B, column D has A and column E has B. It is not highlighting C or D.

But, in another row, Column B has A in it, column C, D and E have B and it is highlighting all the cells.

4. ## Re: Conditional Formatting Question

Is this what you want?

 B C D E 1 2 A B A B 3 C B B A 4 C B C A

If so, select C3:E31 and use this formula in CF
=AND(\$B2<>"",C2<>\$B2)

M.

5. ## Re: Conditional Formatting Question

That did it! Thank you so much.

Do I change the <> to = if I want it to highlight a different color if it matches?

6. ## Re: Conditional Formatting Question

Yes.

=AND(\$B2<>"",C2=\$B2)
Fill --> Green (for example)

M.

7. ## Re: Conditional Formatting Question

That worked. Thank you!

If I want to add a count if formula in there, what would be the best way to do that? In each column (C, D and E), I want to count the number of cells that match Column B. I would need to know the total for number that match for each column.

8. ## Re: Conditional Formatting Question

Where do you want to add the COUNTIF(...) formulas?
In CF formulas? If so, what is the purpose?
In short: what exactly are you trying to do with the results of COUNTIF(...) formulas?

M.

9. ## Re: Conditional Formatting Question

Originally Posted by jmattingly85
I would need to know the total for number that match for each column.
See if this example helps

 B C D E 1 2 A B A B 3 A B A A 4 B B C A 5 6 7 Formulas--> 1 2 1

Formula in B7 copied across
=SUMPRODUCT(--(C2:C4=\$B2:\$B4))

M.