VBA Conditional Formatting Question

[dkv98446]

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

 

[Luke M]

Your quoration marks are messing the VB up. In this line

[pre]

"=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>Opportunities))"
Opportunities is inside a set of quotes, so VB doesn't treat that as a variable, but rather as part of the string. Since you want to have the VB plug in the variable, needs to be outside the quotes, like this:

"=AND(ISNUMBER(SEARCH(""total"",$A2)),ISERROR(SEARCH(""grand"",$A2)), ($J2>" & Opportunities & "))"

[/pre]
Make sense?

 

[dkv98446]

Luke M,

Thank you, had to read/reread your answer a number of times, but it does finally make sense.

Not that I care at this point but I still don't understand why the NUMERIC literal worked as coded.

dkv98446

 

[Luke M]

In the numerical line of code, you had manually inserted the correct number into the formula. In the variable line, you just needed to separate the name of the variable from within the quotes so that VB knows you are talking about a VB variable, and not some Named Range within XL.