I get error code 5 on Conditional formatting macro

[madvogue29]

Could you please help in resolving this ? It works find on one windows laptop but not on the other (Boss's laptop).

Sub AddSalesFormatting()

Worksheets("sales").Activate
ActiveSheet.Unprotect
Worksheets("sales").Cells.FormatConditions.Delete

    
    Range("$C$15:$C$10000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($D14=""Task"",$D13=""Task"",$D12=""Task"",$D11=""Task"",$D10=""Task"",$D9=""Task"",$D8=""Task"")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
    End With
    
    Range("$C$17:$C$10000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($D17<>"""",$D16<>"""",$D15<>"""",$D21<>"""",$D22<>"""",$D23<>"""",$D24<>"""",$D25<>"""")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
    End With
    
    Range("C17:C10003,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""NEW"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With

    Range("C17:C10000,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""PROPOSAL"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 39423
        .TintAndShade = 0
    End With

    Range("C17:C10000,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""NEGOTIATION"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 9655413
        .TintAndShade = 0
    End With

    Range("C17:C10000,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""PENDING"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 16750848
        .TintAndShade = 0
    End With

    Range("C17:C10000,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""ACCEPTED"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 39168
        .TintAndShade = 0
    End With

    Range("C17:C10000,G17:G10003").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""NO GO"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5659387
        .TintAndShade = 0
    End With

    Range("H17:K2373").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G17=""NEW"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
End Sub

Error is here

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($D14=""Task"",$D13=""Task"",$D12=""Task"",$D11=""Task"",$D10=""Task"",$D9=""Task"",$D8=""Task"")"

Thanks in advance!

 

[jasonb75]

Does your boss's laptop have different regional settings? The function "OU" will not be translated to "OR" if your boss's laptop has English language settings.
If that is the case then something like this might work.

Select Case Application.International(xlApplicationInternational.xlCountryCode)
    Case 1
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=OR($D14=""Task"",$D13=""Task"",$D12=""Task"",$D11=""Task"",$D10=""Task"",$D9=""Task"",$D8=""Task"")"
    Case 33
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=OU($D14=""Task"",$D13=""Task"",$D12=""Task"",$D11=""Task"",$D10=""Task"",$D9=""Task"",$D8=""Task"")"
End Select

 

[madvogue29]

I have both French and English laptop that I tested it on and it worked. I thought it would be the R1C1 refence that I suggested to my boss but he has it unchecked as well.

 

[jasonb75]

I thought it might be a #NAME? error being generated by the OU function but I've just tested your code on my laptop (English settings) and I don't get the error, although I will point out that the French language function is not recognised so the condition will not be applied.

I'll have another look in the morning to see if I can find other potential causes.

 

[jasonb75]

I'm finding nothing in your code that should cause the error, most things seem to be related to pivot table names, functions with too many arguments or other similar things, none of which can be associated with your code.

The only possible causes that I can come up with are either the issue detailed in the link below.

VBA - Invalid Procedure Call Error after 2019-08 Windows Update

August 2019 Windows Update causes VBA applications to fail with an

codekabinett.com

or that it could be a compatibility issue in the unlikely event that your boss is still using excel 2003 (or older).

Beyond that, I'm out of ideas.

 

[madvogue29]

Thanks for trying Jason! I am going to try and modify the code with Application.International as you suggested and send it to him. Hopefully it changes something. I will keep you updated

 

[madvogue29]

Hi Jason, Just an update, The internationalization worked fine on my laptop but not on my boss' so I ended up just making a separate sheet with the conditonal formatting that I need and instead of setting the formatting with Macro I just used the macro to copy the formatting from one sheet to another.

Not the solution I was looking for but an solution that did the job.

Thanks again for your help.