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