Large workbook (200+ tabs) validate visible decimal places

[Lowen]

Thanks for reading. Please note I am an intermediate user and I understand how to set formatting (14.692 versus 14.7) and how to apply conditional formatting (if <> then red fill and so on).

Is it possible to use formulas to check whether cells that should show X decimal place are doing so? Our main workbook is used to prepare reports, and we noted that ONE of the tens of pages we print to PDF had 3 decimal places where we only wanted 1.

In other words I'd like to have a check page which would tell me if visible decimal places is what we expect. Do you know a way to test formatting in this way with a formula instead of relying solely on a visual inspection of each number (yes that will be ultimately done anyway, but I'd like to see if I can have excel help me fix prior to this visual check).

Thanks for your suggestions!

 

[Luke M]

Hi Lowen, and welcome to the forum!

With the built-in formulas, there's no way to detect the exact cell formatting that's been applied to a cell; you can only read the cell's value. Since we don't really care about that in this case, that doesn't help us.

I'm going to suggest then using a macro. First though, the phrase"what we want". Since the computer isn't a mind reader then we need to tell it exactly what we want. How do you want to do this? Should all numbers be formatted to 3 decimals? Is it a particular range?