Conditional Formatting Question

jmattingly85

New Member
Joined
Jul 12, 2010
Messages
23
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 
Last edited:

jmattingly85

New Member
Joined
Jul 12, 2010
Messages
23
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
I think misunderstood your 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​

<tbody>
</tbody>


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

M.
 
Last edited:

jmattingly85

New Member
Joined
Jul 12, 2010
Messages
23
That did it! Thank you so much.

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

jmattingly85

New Member
Joined
Jul 12, 2010
Messages
23
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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​

<tbody>
</tbody>


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

M.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,400
Messages
5,450,176
Members
405,590
Latest member
bal016

This Week's Hot Topics

Top