Conditional Formatting for every row

Question

I am trying to highlight the max value in each row of data to determine what year it falls in. Is there a simple way to apply it to the whole spreadsheet? The only way I can do it right now is by using the Format Painter on each individual row... which is not efficient with 800+ rows of data.

Using Conditional Formatting - Top N Items

Answer 1

Here is a tiny macro - you need to copy it to a module in VBA Project. Once there, put a cursor in the macro and press F5 :)

1) it works for rows from 1 to 800 (change it if you need it somewhere else) 2) it works on Sheet1

Sub ManyConditions()

    Dim iRow As Integer

    For iRow = 1 To 800

      With Sheet1

        With .Range(.Cells(iRow, 1), .Cells(iRow, 10)).FormatConditions.AddTop10
          .TopBottom = xlTop10Top
          .Rank = 1
          .Percent = False
          .Interior.PatternColorIndex = xlAutomatic
          .Interior.Color = 13551615
          .StopIfTrue = False
        End With

      End With

    Next

End Sub

Answer 2

The following VBA code could do the trick (updated version):

Sub Mark_max_value_in_every_row()
    Dim rngArea As Range
    Set rngArea = ThisWorkbook.Sheets("Sheet1").UsedRange
    Dim rngRow As Range
    Dim iRow As Long

    For iRow = 2 To rngArea.Rows.Count
        Set rngRow = rngArea.Range(Cells(iRow, 1), Cells(iRow, rngArea.Columns.Count))
        With rngRow.FormatConditions.AddTop10
            .Rank = 1
            .Interior.Color = 49407
        End With
    Next
End Sub

I assume that your first row of the table is headers, therefore you see iRow beginning to count from 2.