Create conditional formatting to indent data by VBA gives runtime error on ExecuteExcel4Macro

[Edgar62]

Hi!

I am trying to create conditional formatting rules via VBA. I often use a custom number format to indent lines of text based on some level indicator. The custom number format is then something like '" "@' to indent by 3 blanks. Then I can embed this within a conditional format with conditions like '=($A1=3)' which is then applied to format text columns according to the level present in column A.

If I am recording this as a macro, the following code is generated:

Sub IndentText()
'
' IndentText Macro
'


'
    Range("B1:B7").Select
    Selection.NumberFormat = """   ""@"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=($A1=3)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ExecuteExcel4Macro "(2,1,""""   ""@"")"
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

When I am trying to execute the macro there is a runtime error on the line with the ExecuteExcel4Macro. (BTW: I am trying this on Excel 2016)

How can I solve this?

Edgar

 

[Edgar62]

Hi!

I am trying to create conditional formatting rules via VBA. I often use a custom number format to indent lines of text based on some level indicator. The custom number format is then something like '" "@' to indent by 3 blanks. Then I can embed this within a conditional format with conditions like '=($A1=3)' which is then applied to format text columns according to the level present in column A.

If I am recording this as a macro, the following code is generated:

Sub IndentText()
'
' IndentText Macro
'


'
    Range("B1:B7").Select
    Selection.NumberFormat = """   ""@"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=($A1=3)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ExecuteExcel4Macro "(2,1,""""   ""@"")"
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

When I am trying to execute the macro there is a runtime error on the line with the ExecuteExcel4Macro. (BTW: I am trying this on Excel 2016)

How can I solve this?

Edgar

Hi!

Actually it was easy to fix by not using the macro recorder:

Instead of the ExecuteExcel4Macro call, I use now

   Range("B1:B7").Select
    For Level = 1 To 3
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=($A1=" & Level & ")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        ' Indentation by Level
        Selection.FormatConditions(1).NumberFormat = """" & String(Level, " ") & """@"
        Selection.FormatConditions(1).StopIfTrue = False
    Next Level

So this thread can be closed.

Edgar