Excel VBA for Conditional Formatting

  • I am having a problem when creating a formula using VBA code.


    The following is what I pass to a Sub Routine is below it.


    ----------------------------------------------------------------------------------------------------------


    Set My_Range = Worksheets("Long Log Sheet").Range("DL10:DL1965")


    Run_Conditional_Formating_1 My_Range, xlExpression, xlEqual, "=if(BZ10<>"",if(BZ10<=$BZ$6,true,if(BZ10>=$BZ$5,true)))", "", vbRed, _

    xlExpression, xlEqual, "=if(BZ10<>"",if(BZ10<=$BZ$4,true,if(BZ10>=$BZ$3,true)))", "", vbYellow, _

    xlCellValue, xlEqual, "", "", vbYellow


    When I pass the following I get ==> "error 5 - invalid procedure call"


    ----------------------------------------------------------------------------------------------------------


    If i pass the following it will pass I can get the expressions into the correct Conditional Formatting areas but they are always encases it with Double Quotes "".


    Run_Conditional_Formating_1 My_Range, xlExpression, xlEqual, "AND(CD10<>"",NOT(AND(CD10>=$CD$6,CD10<=$CD$5)))", "", vbRed, _

    xlExpression, xlEqual, "AND(CD10<>"",NOT(AND(CD10>=$CD$4,CD10<=$CD$3)))", "", vbYellow, _

    xlCellValue, xlEqual, "", "", vbYellow


    Here is the Sub Routine


    ----------------------------------------------------------------------------------------------------------


    Public Sub Run_Conditional_Formating_1(Rng As Range, ConditionType1 As Integer, Operator1 As Integer, Condition1_1 As String, Condition1_2 As String, Color1 As String, _

    ConditionType2 As Integer, Operator2 As Integer, Condition2_1 As String, Condition2_2 As String, Color2 As String, _

    ConditionType3 As Integer, Operator3 As Integer, Condition3_1 As String, Condition3_2 As String, Color3 As String)

    Dim cond1 As FormatCondition

    Dim cond2 As FormatCondition

    Dim cond3 As FormatCondition


    If Condition1_1 <> "" Then

    cond1 = Rng.FormatConditions.Add(ConditionType1, Operator1, Condition1_1, Condition1_2)

    cond1.Interior.Color = Color1

    End If


    If Condition2_1 <> "" Then

    Set cond2 = Rng.FormatConditions.Add(ConditionType2, Operator2, Condition2_1, Condition2_2)

    cond2.Interior.Color = Color2

    End If


    If Condition3_1 <> "" Then

    Set cond3 = Rng.FormatConditions.Add(ConditionType3, Operator3, Condition3_1, Condition2_2)

    cond3.Interior.Color = Color3

    End If


    End Sub


    This is really frustrating. Any help would be super appreciated. I am sure it is a simple fix.