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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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,248
Messages
5,449,242
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top