Conditional Formatting Question

[jmattingly85]

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?

Thank you in advance!

 

[Marcelo Branco]

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.

 

[jmattingly85]

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.

 

[Marcelo Branco]

I think misunderstood your question

Is this what you want?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD]

A​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

C​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD="bgcolor: #FFFF00"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD]

C​

[/TD]
[TD="bgcolor: #FFFF00"]

A​

[/TD]
[/TR]
</tbody>[/TABLE]


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

M.

 

[jmattingly85]

That did it! Thank you so much.

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

 

[Marcelo Branco]

Yes.

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

M.

 

[jmattingly85]

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.

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

I would need to know the total for number that match for each column.

See if this example helps


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD="bgcolor: #92D050"]

A​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

A​

[/TD]
[TD="bgcolor: #FFFF00"]

B​

[/TD]
[TD="bgcolor: #92D050"]

A​

[/TD]
[TD="bgcolor: #92D050"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD="bgcolor: #92D050"]

B​

[/TD]
[TD="bgcolor: #FFFF00"]

C​

[/TD]
[TD="bgcolor: #FFFF00"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Formulas-->​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


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

M.