a code to trace pure numbers formatted as Text

[swordsman]

Dear All,

in a world full of bugs...
and in worksheets programmed to function as required...

Can someone please avail a code/macro that can highlight a cell<s> which
contains pure numbers yet are formatted as "Text".

I need to assure that even when there comes a bug to change the cell from
any formatted number into a text, i can still review easily and verify the
worksheet to assure integrity on my logical-numeric based- formulas.

TIA

 

[Dave Peterson]

I'm not sure what pure numbers are, but this may work in most cases.

But it does assume that you're not using format|Conditional formatting for
anything else.

Select your range (I used A1:x99).
With the activecell A1, use
Format|Conditional formatting
formula is: =AND(ISNUMBER(-A1),ISTEXT(A1))
and give it a nice shade.

Be aware that these will look like they can be numbers to excel:
'1E3
'January 1, 2007
'00:12:30

1E3 = 1*10^3 = 1000
and dates and times are numbers to excel.

 

[swordsman]

that's a treat,

I tried it on two cells, it does provide shining colors for numbers
formatted as text.
Problem is, upon knowing through the colors, when I change these cells from
Text to General, the cells do not update even when I close/save and open the
file...
It looks like it is a must for me to press always f2...on each cells <with
pure numbers> in order that my excel recognize the change in the format and
*formula*...
Is there any other way we can automate this action <change format from text
to general> while the logical formulas <in the cell or other cells linked
here and in the cells Cond. Format> automate itself as well ?

Cheers!

 

[Dave Peterson]

Changing the format doesn't change the value of the cell.

Format the cells as General (or anything but text)
One quick way to change text numbers to number numbers is to select an empty
cell.
Edit|copy that cell
select the range to fix
edit|paste special|check add and values

 

[swordsman]

thanks Dave,

I will wait until someone can shorten the complete trick by a macro for a
multiple sheets workbook.

best wishes