Find largest number in my range but not to include the active cell

[danbates]

Hi,

Please can someone help?

If it is possible at all, I would like to edit this code so when it searches in the range for the largest number, it doesn't include the cell that has just been edited.

Sub ShowMax(Optional ByVal Target As Range)

    Dim TheMax As Double
    
    TheMax = WorksheetFunction.Max(Range("D6:BM20"))


End Sub

Any help would be much appreciated.

Regards

Dan

 

[mikerickson]

Sub ShowMax(Optional ByVal Target As Range)

    Dim TheMax As Double
    
    TheMax = WorksheetFunction.Max(Range("D6:BM20"))
    If Target.Value = TheMax Then TheMax = Worksheetfunction.Large(Range("D6:BM20"), 2)

End Sub

 

[danbates]

Hi mikerickson,

That's perfect. Thank you so much for your help.

Thanks

Dan