Help with VBA creating new conditional formatting rules

[braindiesel]

I am running ito an error re ExcecuteExcel4MAcro line
This code was produced by the recorder.

Any ideas?

Range("L5").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MATCH(F5,myFactors,0)>=6"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ExecuteExcel4Macro "(2,1,""0 x"")"
    Selection.FormatConditions(1).StopIfTrue = False
    Range("L5").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MATCH(F5,myFactors,0)=3"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ExecuteExcel4Macro "(2,1,""0""'' Centers"""")"
    Selection.FormatConditions(1).StopIfTrue = False
    Range("L5").Select

 

[rlv01]

Assuming that your Excel 4 macros work when you run them manually ,then you are probably running into syntax issues with the ExecuteExcel4Macro method.

Application.ExecuteExcel4Macro method (Excel)

Office VBA reference topic

docs.microsoft.com

Excel 4 macros are a pain. Do yourself a favor and convert them to VBA macros.

 

[Fluff]

What are those xl4 macros doing?

 

[braindiesel]

Copying and pasting wasn't pasting the conditional formatting so I decided to use the Macro recorder to rewrite the conditional formatting from scratch and it wrote the code this way... and the code unedited does not work. Thus, I am here.

The intent is that based on an adjacent cell, I want the targeted cell formatted.
If the adjacent cell says "Centers"and they enter a 14, I want the cell to say 14'' Centers but the ability is retained to use the 14 for mathematical calculations.
I have 4 rules in total for the cell...... I only copied the code for two as the other two are similar
It has worked without issue when written from scratch... but when a user deletes it I want code to rewrite it. Thus I am here.

 

[Fluff]

Are you saying that the macro recorder recorded the use of the XL4 macro?
If so how did you change the cell format?

 

[braindiesel]

I have been using Excel and VBA for years. I did not intentionally do anything to change this... I had an update in the past few days... not sure if something internal triggered this.

But yes... the macro recorder recorded the use of the XL4 Macro... and I do not know why.

 

[Fluff]

Ok, delete the xl4macro lines & replace them with something like

    Selection.FormatConditions(1).NumberFormat = "0""'' Center"""

 

[rlv01]

    With Range("L5")
        .FormatConditions.Delete
        With Range("L5").FormatConditions.Add(Type:=xlExpression, Formula1:="=MATCH(F5,myFactors,0)>=6")
            .NumberFormat = "# ""x"""
            .StopIfTrue = False
        End With

        With Range("L5").FormatConditions.Add(Type:=xlExpression, Formula1:="=MATCH(F5,myFactors,0)=3")
            .SetFirstPriority
            .NumberFormat = "# ""'' centers"""
            .StopIfTrue = False
        End With
        .Select
    End With

 

[braindiesel]

I never replied back... apologies... This worked. Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.