Conditional Formatting - Highlight Unequal cells

Mitchyb1

New Member
Joined
Jun 23, 2009
Messages
4
Hi - here is my dilemma:

Two columns such as the two below.. (they are both results of division formulas and need to remain formulas)
<TABLE style="WIDTH: 106pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=141 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num="0.23174999999999998">23.2%</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=77 x:num="0.23177106852584622">23.2%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.19425000000000009">19.4%</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.19439978922598486">19.4%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.32550000000000001">32.6%</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3251992667755001">32.5%</TD></TR></TBODY></TABLE>

I have the decimals rounded to 1 decimal only. I need to highlight the cell values in the column in the right that do not match the cell values in the column on the left. Below is what is causing the problem:

23.2% matches 23.2% when rounded to one decimal but they do not match if you expand the decimal places. (Example: 23.2224432 vs. 23.211445544 -- these values do not match) I am only concerned with the 23.2 (one decimal place) value--not the 23.211445544 value.

Is there a way to have a conditional format that says if B2 is not equal to A2 with one decimal place showing, then highlight Red?

THANK YOU!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Rather than formatting A2 and B2 to show 1 decimal place you could change the formulas in those cells to incorporate ROUND function, e.g.

=ROUND(formula,1)

or use this formula in conditional formatting

=ROUND(A2,1)<>ROUND(B2,1)

format red
 

Mitchyb1

New Member
Joined
Jun 23, 2009
Messages
4
Thank you -- I used the conditional format formula and it works! Magic! Thanks again!
 

Forum statistics

Threads
1,136,274
Messages
5,674,777
Members
419,524
Latest member
helensesc

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
Top