Home / User Contributed Macros / Highlight Largest Value

Highlight Largest Value

This Macro will highlight the maximum value in a column with bold font style. 

For a column on edit:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = false
On Error Goto ENEV
REM add your code below
 
     Dim MaxCellRange,SearchRange as Range 
     Set SearchRange = Range("A1:A65536") 
 
     If Target.Column = SearchRange.Column    Then  

         Dim MaxPos as Integer
 
         SearchRange.Font.Bold = false
 
         MaxPos = WorksheetFunction.Match(WorksheetFunction.Max(SearchRange),SearchRange,0)
         set MaxCellRange = SearchRange.cells(MaxPos,1)
 
         MaxCellRange.Font.Bold = true
 
     End If

ENEV:
Application.EnableEvents = true
End Sub


For the selected Range:
Public Sub Bold_Max_Num()
 
     Dim MaxPos as Integer
     Dim MaxCellRange as Range
 
     MaxPos = WorksheetFunction.Match(WorksheetFunction.Max(Selection),Selection,0)
     set MaxCellRange = Selection.cells(MaxPos,1)
 
     MaxCellRange.Font.Bold = true

End Sub


    		 RSS of this page