VBA not recognizing [Red] number format-CodePudding

I'm writing a simple code shown below, however excel doesn't seem to recgonize the [Red] number format, and is changing it to"#.##0,00" any thoughts on how to fix this? I have tried other number formats and it always seems to be the one with [Red] not being recognized.

Additionally is there a way I can shorten the code by not having to copy "Or c.NumberFormat " every time?

Thanks!

CODE BELOW

Dim c As Range

For Each c In Selection

    If Not c.NumberFormat = "0%" Or c.NumberFormat = "0%;(0)" Or c.NumberFormat = "0%;[Red](0%)" Then c.NumberFormat = "#.##0,00"

Next

End Sub

CodePudding user response：

I am almost sure your problem has nothing to do with the Red formatting.

In your current If-statement, the Not operator is negating only the first part, so the format "#.##0,00" is set to all cells that don't have the format "0%"

What you mean is

If Not (c.NumberFormat = "0%" Or c.NumberFormat = "0%;(0)" Or c.NumberFormat = "0%;[Red](0%)") Then

which can be changed (using simple boolean algebra, that's not VBA) to

If c.NumberFormat <> "0%" And c.NumberFormat <> "0%;(0)" And c.NumberFormat <> "0%;[Red](0%)" Then

In an If-statement, there is no shortcut like If c.NumberFormat = "0%" Or "0%;(0)" Or "0%;[Red](0%)"

You can assign the format to a variable to make the If-statement shorter. This also has the advantage that the code is slightly faster as it reduces the round-trips between VBA and Excel

Dim nf As String
nf = c.NumberFormat
If nf <> "0%" And nf <> "0%;(0)" And nf <> "0%;[Red](0%)" Then

Or you could use the Select case-statement

Select Case c.NumberFormat
    Case "0%", "0%;(0)", "0%;[Red](0%)"
    Case Else
        c.NumberFormat = "#.##0,00"
End Select