Conditional Format on a column that contains numbers and text in different cells - Need to highlight when it changed from last report

[gisquared]

I need help.
I am doing a simple conditional format on a report, I have an OLD tab and a NEW tab. The condition is on the NEW tab.

The condition is set up =A1<>'OLD'!A1 it is applied to cell A1:CH3000.

Most of the sheet is working great... if a date doesn't match a date it's getting highlighted. If a text field doesn't match a cell that has text in it ... it's getting highlighted.

The problem is this one column that is exported from my Access Data Base. This column contains BOTH numbers & TEXT.
My cell CC2 on New is the exact same number as CC2 on OLD... but it is highlighting that the 2 cells do not match.

If I go into cell CC2 on the New file to look for a blank, then the highlight goes away. Not sure what to do to fix this.

Anyone have any ideas? The yellow is true deltas... the rest of them should be clear of color.

 

[Peter_SSs]

I am guessing that one column is Text and one column is Number or General.
Go into each of the sheets and for a row where the values look the same but are highlighted (I have used row 2) use a blank cell with this formula and compare the results.
=ISNUMBER(CC2)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[gisquared]

Thank you for that. Yes somehow they were different. I know I did format painter like 20 times...

I had to go through and multiply the "number" by 1 and found the texts and copy them from original column to the calculated column, then Copied all the values and pasted values and number formats. It seems to be working now.

I have VBA code somewhere that will open up the Excel File and do all of this for me... but was trying to make things simple... LOL.

I appreciate the guidance. Hopefully I (or the people I'm writing this for) got it fixed for future data dumps. I'm having people paste special values in this so not to overwrite my formats.


Also... thanks for the tip about fixing my profile... I did it. :D

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.