Need help with macro for conditional formatting using number forma

[Kathy]

I need help with a macro for conditional formatting cells using the number
format. I can get it to work for bold font or cell color but not number
format when I am recording it. For this I recieve the following when
recording:

ExecuteExcel4Macro "(2,1,""#,##0.00_);[Red](#,##0.00)"")"


I tired re-writing it but without success. I tried recording it many
different ways. The macro sees this as an error.

Is Excel 4 a hidden macro?

is this a bug? If so how do I get around it?

 

[OssieMac]

Hi Kathy,

I assume you are using xl2007 to get the results you posted with recording
the macro. Try the following. Note that a space and underscore at the end of
a line is a line break in an otherwise single line of code. (I use them to
avoid undesired breaks in these posts which cause errors when copied into the
VBA editor.)

To get the correct format, you can actually use the number format in the
interactive mode. Select the required format then Custom and Copy the format
and just put between double quotes in your VBA.

I have applied the format to one cell only and then copied to format to the
remaining cells requiring it. I suggest you do the same as per the code.

I have set 5 conditions in the following code.

Sub SetConditFormat()

'Ensure that conditional formatting is
'cleared from entire range to be
'conditionally formatted.
With Sheets("Sheet1").Range("H1:H29")
.FormatConditions.Delete
End With

'Set conditional format for one cell
With Sheets("Sheet1").Range("H1")

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=H1<=5"

.FormatConditions(1).NumberFormat _
= "$#,##0.00"
.FormatConditions(1).StopIfTrue = True

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=H1<=10"

.FormatConditions(2).NumberFormat _
= "0.00"
.FormatConditions(2).StopIfTrue = True

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=H1<=15"

.FormatConditions(3).NumberFormat _
= "0.000"

.FormatConditions(3).StopIfTrue _
= True

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=H1<=20"

.FormatConditions(4).NumberFormat _
= "0.0000"

.FormatConditions(4).StopIfTrue = True

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=H1>20"

.FormatConditions(5).NumberFormat _
= "0.000000"

.FormatConditions(5).StopIfTrue = True

End With

'Copy conditional format to other cells
With Sheets("Sheet1")

.Range("H1").Copy

'Include the copied cell in the Paste range
.Range("H1:H29").PasteSpecial _
Paste:=xlPasteFormats
End With

End Sub