Conditional Formatting Question

jmattingly85

New Member
Joined
Jul 12, 2010
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
That did it! Thank you so much.

Do I change the <> to = if I want it to highlight a different color if it matches?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top