CODEWITHSUNDEEP
excelvbaconditional-formatting
Danny Coleiro
Danny Coleiro

Reputation: 127

Conditional Formating macro gives me Run-time error 1004 error

Open an excel sheet

Type Orange into A1 cell

Type Banana into A2 cell

Type 15 into B1 cell

Type 23 into B2 cell

Go to Developer Tab and then click Record Macro button

Click OK in order to record a macro.

Select A1 and A2 cells together

Go to Home tab > Conditional Formating > New Rule > Use a formula to determine which cells to format

Type =B1>12

Click Format button

Click Number tab

Click Custom button

Type " "@

Click OK button

Click OK button again

Go to Developer Tab and then click Stop Recording button

Press Alt+F11 in order to see following macro.

Sub Macro1()
Range("A1:A2").Select
Selection.NumberFormat = """""@"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1>12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,""""""@"")"
Selection.FormatConditions(1).StopIfTrue = False
End Sub

You will see the error (Run-time error 1004) when you run the macro you recorded.

Do you know how to solve Run-time error 1004?

Upvotes: 2

Views: 464

Answers (1)

Wito
Wito

Reputation: 303

I did a little research and I found your solution here The macro recorder is reverting to the old ExecuteExcel4Macro command in order to set the number format for the conditional formatting rule. This is not necessary.

Below how your code should look like:

Sub Macro1()

With ActiveSheet.Range("A1:A2")
     .FormatConditions.Delete 'Removes all the rules from the selection first
     .FormatConditions.Add Type:=xlExpression, Formula1:="=B1>12"
     .FormatConditions(.FormatConditions.Count).SetFirstPriority
     .FormatConditions(1).NumberFormat = """""@"
     .FormatConditions(1).StopIfTrue = False
End With
End Sub

Upvotes: 2

Related Questions