CODEWITHSUNDEEP
vbaexcel
Alex S
Alex S

Reputation: 3

VBA Conditional Formatting with variable in formula

Trying to create a relatively simple macro to cycle through every sheet apart from the one at the start (named) and apply conditional formatting to all of the used cells within a specific column, based off the numerical average of the cells in question.

I've gotten stuck with getting the conditional formatting formula to enter correctly into the cell.

Example: The formula I want is =R2>2(Average(R2:R*)) (where * is the last used row)

However, what's im getting in the formula box is: ="R2>2(Average(R2:R230))" (230 is the last used row on that sheet)

Removing the speechmarks from the script breaks it with "Expected:End of Statement"

Code here, because there might be other stuff wrong with it too:

Sub Bread()
    Dim LastRow As Long

    For Each Sheet In Sheets

    If Sheet.Name = "Base Details" Then

    Else
        Sheet.Cells.ClearFormats

        LastRow = Sheet.UsedRange.Rows.Count

            With Sheet.Range("R2:R" & LastRow)
                .FormatConditions.Add Type:=xlExpression, Formula1:="R2" & ">" & "2" & "(" & "Average" & "(" & "R2:R" & LastRow & "))"
                With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                With .Interior
                     .Color = RGB(255, 0, 0)

                End With
                End With

            End With
            With Sheet.Range("R2:R" & LastRow)
                .FormatConditions.Add Type:=xlExpression, Formula1:="R2" & "<" & "0.1" & "*" & "(" & "Average" & "(" & "R2:R" & LastRow & "))"
              With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                With .Interior
                     .Color = RGB(255, 255, 0)

                 End With
                 End With

            End With

    End If


Next Sheet


End Sub

Thanks a lot!

Upvotes: 0

Views: 3730

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

I think this is what you're looking for:

Sub Bread()

    Dim ws As Worksheet
    Dim LastRow As Long

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Base Details" Then
            ws.Cells.ClearFormats
            LastRow = ws.UsedRange.Rows.Count
            With ws.Range("R2:R" & LastRow)
                .FormatConditions.Add Type:=xlExpression, Formula1:="=R2>2*Average($R$2:$R$" & LastRow & ")"
                .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 0, 0)
                .FormatConditions.Add Type:=xlExpression, Formula1:="=R2<0.1*Average($R$2:$R$" & LastRow & ")"
                .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 255, 0)
            End With
        End If
    Next ws

End Sub

Upvotes: 1

xidgel
xidgel

Reputation: 3145

I see two problems. First as Scott Craner noted, you're missing an operator between 2 and Average. Second, you need an = at the beginning of your formula (before the first instance of R2):

Formula1:="=R2" & ">" & "2 * " & "(" & "Average" & "(" & "R2:R" & LastRow & "))"

Hope that helps.

Upvotes: 0

Related Questions