check format of cells using if statement - "custom" format

Hiya,

I'm having trouble with some code.
Data is downloaded from a machine in "custom" format. The cells show "5.5" but if you click in them, the bar shows that the number is actually "5.455", so I suppose that is the customised part. This is fine and data can be analysed in custom format. Later in the macro, I format the data as "number", showing all 3 decimal places in the cells. Format= "0.000"

I need to create an error checking section of the macro.
I need to ensure that no one accidentally types another symbol into the data. For example, "%5", "5%" , "5£", or "£5". I am error checking using "if Not IsNumeric then... msg box"
This check works for "%5" and "5£", however if "5%" is into a cell then the format changes to be in percentages and the IsNumeric function fails.

I therefore want to write this code out correctly

Please Login or Register  to view this content.

I know that the only line that doesn't work is :
If Not Ccell.NumberFormat = "custom" And Not Ccell.NumberFormat = "0.000" Then
because if I write "if Ccell.NumberFormat = "0%" it displays the msg. (i don't want to have to do this for currency, time, etc)

Currently, the code just says all the custom format cells are errorenous, so " Ccell.NumberFormat = "custom" " clearly doesn't work!!

please can anyone help me? XXX

Have you tried just:
if not Ccell.NumberFormat = "0.000"
without the custom part?

Note, in your code you compare the same property (numberformat) against two different values, it can only be one of them so everything will display the error message. When you select a custom format, the numberformat is set to the custom format you type in - use the macro recorder and record yourself changing the cell format if you are unsure what it would be in VBA.