Conditional formatting top 12 help needed

Copper Contributor



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 the top 12 in the sheet). I’ve tried dragging down and tried copy and paste but it isn’t doing what I want it to do. Please help! 

3 Replies


You'd have to use the Format Painter one row at a time, instead of applying it to all rows at once.

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!


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