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.