CODEWITHSUNDEEP
excelvba
nick lanta
nick lanta

Reputation: 638

Sub-defined error in the LARGE part of this formula?

I have a conditional formatting formula that I recorded, but I want to make it dynamic. I understand I need to add in LastRow and stuff like that, but I keep getting the quotations screwed up.

this is it recorded: 

Formula1:"=CA$50452>=LARGE($CA$50452:$DO$50452,10)"

but I want to make it like this: 

Formula1:"=CA2:CA" & LastRow >= "Large(CA2:CA" & LastRow & ")", 10)

but I keep getting a Sub-defined error on the LARGE part of the formula.

Any thoughts?

rest of script: 

Sub Top10()

Dim ws As Worksheet
Set ws = Worksheets("Sheet111")

Dim LastRow As Long
LastRow = Range("CA" & Rows.Count).End(xlUp).Row

Dim LastCol As Long
LastCol = ws.Cells(LastRow, ws.Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(LastRow + 1, "CA"), ws.Cells(LastRow + 1, LastCol)).Formula = "=SUM(CA2:CA" & LastRow & ")"

Range("CA2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=CA2:CA" & LastRow & ">=LARGE(CA2:CA" & LastRow & "), 10)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

Upvotes: 0

Views: 28

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

Formula1:"=CA" & LastRow + 1 & " >= Large(" & ws.Range(ws.Cells(LastRow + 1, "CA"), ws.Cells(LastRow + 1, LastCol)).Address(1,1) & ", 10)"

Also remove the line:

Range("CA2").Select

And replace all the Selection

With:

ws.Range(ws.Cells(2, "CA"), ws.Cells(2, LastCol))

Upvotes: 1

Related Questions