Hi I have recorded a macro to use as a basis to create some Conditional Formatting, however, I am not able to correctly rewrite it with variables for some reason.
When I look at the resultant of my edited macro, it basically changes it from a Cell Value statement to a Formula statement when I look at "Manage Rules" and gives me a TRUE result for all cells
Here is the recorded macro line:
Range("E11:J38").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""<$K11*$G$3"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
This was my attempt to use variables:
FirstRow = 11
LastRow = Cells(10, 2).End(xlDown).Row
LastCol = Cells(10, 2).End(xlToRight).Column
BeginCellCondFormat = Cells(11, 5).Address(False, False)
EndCellCondFormat = Cells(LastRow, LastCol - 3).Address(False, False)
AverFirstCondFormat = Cells(FirstRow, LastCol - 2).Address(False, False)
AverLastCondFormat = Cells(LastRow, LastCol - 2).Address(False, False)
VarRuleLow = Cells(3, 7).Value
VarRuleHigh = Cells(3, 8).Value
Range(BeginCellCondFormat & ":" & EndCellCondFormat).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=" " < AverFirstCondFormat & " * (" & VarRuleLow & ")"
' "=""<$K11*$G$3"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Unfortunately, when reviewing the original conditional format manually input, it says correctly:
Cell Value < $K11*$G$3 Applies to =$E$11:$J$38
While the macro replacement is
Formula =TRUE and applies to =$E$11:$J$38
I am sure this is just a syntax issue, but what is the issue?
Bookmarks