VBA Conditional Formatting with variable in formula

Question

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!

This doesn't solve your problem, but I believe the following would be much more readable: Formula1:="R2>2*(Average(R2:R" & LastRow & "))" You say that you are getting 230 and 230 is the last used row on the sheet. You also say that you want the last used row, so I'm confused about what the problem is. — JNevill
@JNevill Soryr, I've not made it clear enough. The problem I'm getting is that when the formula is added to the conditional formatting, it gains additional speechmarks - see straight after the =. Thanks for the updated text, that does make it easier to read. — Alex S
Oh! I see. The formula you are setting is surrounded in double quotes. That makes sense now. I was really scratching my head on that one. — JNevill
It looks like the two answers have you covered. You just need one more = sign there like Formula1:="=YourFormula" — JNevill

tigeravatar tigeravatar · Accepted Answer · 2018-05-15T15:21:20

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

VBA Conditional Formatting with variable in formula

2 Answers