Forum Discussion

roxyb2240's avatar
roxyb2240
Copper Contributor
Nov 17, 2022

Conditional formatting top 12 help needed

Hello   i have applied conditional formatting to find the top 12 across a row. I now need to do this for the rest of the rows in my sheet, and find the top 12 number across each row (rather than th...
excel
roxyb2240's avatar
roxyb2240
Copper Contributor
Nov 17, 2022
Thanks Hans, that’s what I’m currently doing as a workaround because it lets me do it one row at a time but if I try and paste any more than that it doesn’t work. The only problem is I’ve got thousands of rows!
HansVogelaar's avatar
HansVogelaar
MVP
Nov 17, 2022

roxyb2240 

You might run a macro:

Sub Top12Formatting()
    Const FirstRow = 2 ' change if needed
    Dim CurRow As Long
    Dim LastRow As Long
    Application.ScreenUpdating = False
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & FirstRow & ":A" & LastRow).EntireRow.FormatConditions.Delete
    For CurRow = FirstRow To LastRow
        With Range("A" & CurRow).EntireRow.FormatConditions.AddTop10
            .TopBottom = xlTop10Top
            .Rank = 12
            .Interior.Color = vbRed
        End With
    Next CurRow
    Application.ScreenUpdating = True
End Sub

Resources