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 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! 

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