write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Saturday, 11 February 2012

Excel Find and Highlight Maximum Value from Each Column

this will highlight maximum value from each column in worksheet




Sub Mtest()
Dim c As Range, rng As Range
Dim lCol As Long, LR As Long
With ActiveSheet
    For lCol = 1 To .UsedRange.Columns.Count
      LR = .Cells(.Rows.Count, lCol).End(xlUp).Row
      Set rng = .Range(.Cells(1, lCol), .Cells(LR, lCol))
      For Each c In rng
        If c.Value = Application.WorksheetFunction.Max(rng) Then
          c.Interior.ColorIndex = 3
        Else
          c.Interior.ColorIndex = xlNone
        End If
      Next
    Next lCol
  End With
End Sub

No comments:

Post a Comment