asked on

Help with a conditional format (VBA)

Hi all,

I'm attempting to run the following code, which loops through various ranges on a worksheet installing conditional formats at these ranges using different formulas, but getting a Run Time '1004' error. The line that fails is:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Formula(a) (n.b. its the second line of code inside the loop)

Obviously Excel is not digging the way I'm using the variable 'Formula(a)' at the end of the line. Any suggestions on how to finish this line, or use the variable?
cheers!

Sub conFors()
    Dim a As Integer
    Dim RangeNum(3) As String
    Dim Formula(3) As String
    
    RangeNum(0) = "B2:B3"
    RangeNum(1) = "B5"
    RangeNum(2) = "B11:B50"
    RangeNum(3) = "F11:F50"
    
    Formula(0) = "=ISBLANK(B2)=TRUE"
    Formula(1) = "=ISBLANK(B5)=TRUE"
    Formula(2) = "=IF((AND(ISBLANK(B11)=TRUE,ISBLANK(A11)=FALSE)),TRUE)"
    Formula(3) = "=IF((OR(ISBLANK(A11)=FALSE,ISBLANK(B11)=FALSE))*((AND(ISBLANK(F11)=TRUE))),TRUE,FALSE)"

    For a = 0 To 3
        Range(RangeNum(a)).Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Formula(a)
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 90
            .Gradient.ColorStops.Clear
        End With
        With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Next

    
End Sub

Open in new window

patrickab

In both instances you have only allowed for 3 variables:

Dim RangeNum(3) As String
Dim Formula(3) As String

when in fact you have 4 variables. Thus make them:

Dim RangeNum(4) As String
Dim Formula(4) As String

Patrick

Tracy

You can also change your base array number to 1 by putting this at the top of your module:

Option Base 1

This way, you'll start your array's at 1-4, instead of 0-3, which are actually both equal to 4 options as Patrick has pointed out. But this way, it may be a little less confusing.

Limey1212

ASKER

Ok,
but the arrays are not the issue, and surely there are four in the code, (a count of objects 0-3 would be four would it not?). I've never used Option Base 1 because to me that is confusing, and not good form.

the issue I'm having is with the second line of the loop:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Formula(a)
this line fails obviously the way I've used the variable is incorrect, the question is how would I use it??

patrickab

Try using a word other than Formula...

Patrick

ASKER CERTIFIED SOLUTION

Limey1212

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial