Can someone explain to a VBA dropout why when $J2 is compared to the numeric literal 5 (see yellow highlighted code) the conditional formatting works, but does not work when $J2 is compared to the variable Opportunities (see gray highlighted code)?
Option Explicit
Dim Opportunities As Integer
Sub Macro8()
'
' Macro8 Macro
'
Opportunities = InputBox("How Many Serving Opportunities")
MsgBox Opportunities
Range("A2:J400").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>Opportunities))"
' Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
' "=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>5))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
MsgBox Opportunities
End Sub
Option Explicit
Dim Opportunities As Integer
Sub Macro8()
'
' Macro8 Macro
'
Opportunities = InputBox("How Many Serving Opportunities")
MsgBox Opportunities
Range("A2:J400").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>Opportunities))"
' Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
' "=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>5))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
MsgBox Opportunities
End Sub