Glitch with Conditional Formatting

[Milleby]

Hey guys,
I'm having a problem with conditional formatting where I've instructed Excel to highlight cells pairs containing unique values with a yellow color scheme. I've done this in many places throughout the spreadsheet, but in two particular locations, for whatever reason, conditional formatting is highlighting the cell pairs when values are duplicates and I'm not sure what's causing the issue.

I've checked the rules for each cell pair, and they seem to look okay...

I'm including a few screenshots:
Capture 1.PNG
I'm using red line segments to show the call pairs that become highlighted when the values don't match. As you can see, all of them work fine except for the one that is activated in the encircled area as both values are $706.81.

Oddly, if I hard enter $706.81 into the cell on the left, the highlighting goes away. This does NOT happen if I hard enter $706.81 into the cell on the right.

Here is another image of the error that shows the conditional formatting rule used for this cell pair:
Capture 2.PNG

This occurs in one other place in the spreadsheet, and the dollar amounts are exactly the same as the ones in the above screenshots (the cells in the above screenshots reference the cells in the other place where this error is occurring).

Anyways, if anyone has every experienced/troubleshot this error before I would really appreciate some help... I'm actually pretty embarrassed to show this because my finances are in an abysmal state right now...

[David A Coop]

Hi Milleby,

First comment, you should avoid posting personal or sensitive data.

Second, we can't see the condition you have used - just the range, so it's not possible to assist.

Third, it would be better to attach a desensitisedcopy of the workbook. That makes it easier to see what is happening.

Regards

David

[Milleby]

A few details have been altered, but I did have to leave many things unedited for the sake of demonstrating the problem. In any case, I'm pretty confident that there's nothing unsafe included in that post. For now I'm going to post a screenshot of the formula and hope that that will be enough of a clue. If necessary, I will make a desensitized version of the spreadsheet and post it up here, but that would be a pretty time consuming task that I'd prefer to avoid if possible.
Capture 3.PNG
This is simply a standard unique values rule.

[Andy Pope]

Have you check the actual content of the 2 cells. Is it possible they look the same due to rounding?

[davsth]

As a starting point if you put in a cell =c19=d19 if the values are not unique (eg the same) it will return true otherwise false. What does it evaluate when the "error" occurs? I suspect that as it went away when you typed it, the value in c19 is not what you say it is. It is the underlying value not the format the value is being displayed in

[David A Coop]

A couple of observations!

You have some merged cells which can cause some unexpected results. Avoid these.

Instead of pointing to just C19:D19 - have you tried setting the range to C9:D24?

Regards, David

[FDibbins]

Oddly, if I hard enter $706.81 into the cell on the left, the highlighting goes away. This does NOT happen if I hard enter $706.81 into the cell on the right.

That kinda tells me that the left cell is NOT exactly 706.81, but the RHS 1 is. do a quick check =left-cell=right-cell FALSE tells you they are not the same

[David A Coop]

Combining what Ford, Andy, davsth and myself have said.

Instead of using the Format Unique Cells option, use a formula (next row down in the rule type box).

Enter =ROUND($C9,2)<>ROUND($D9,2) - this takes care of rounding differences where one of the cells is not strictly to two decimal places.

If you copied this down over the whole range, your totals would also be highlighted. I guess it depends on how you want to present your data.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Milleby]

Hey guys, so it turns out that one of the values was actually 706.8099999999 or something, so rounding it worked. I'm far too busy right now to obsess over what's causing this, but I am baffled because all of the calculations that went into determining the value of that cell consisted of addition and subtraction between simple dollar amounts. I looked over the precedent cells pretty thoroughly and didn't see anything that went beyond the 2nd decimal place. No idea what could possibly be causing this.