Please help with application.max

  • :puzzled:

    Hi all,

    Can someone help me with the vba code to find the maximum value in a column. My spreadsheet incorporates several columns Agent Number, Date, and Sales are the main concerns. I want an automatic procedure where my macro hightlights the maximum sales of each agent. How can adapt the following macro to suit my needs.


    Sub Find_Max()
    Dim searchRange As Range
    Dim cell As Range
    Dim maxValue, maxRow As Integer
    Set searchRange = Columns("L")
    maxValue = Application.Max(searchRange)
    maxRow = Application.Match(maxValue, searchRange, 0)
    Set cell = Cells(maxRow,12)
    End Sub

    Any help on this would greatly appreciated.


  • I'm not sure what you've got the problem with. Is it just getting the cell color to change? If so, then I merely tweaked your declarations slightly and added a line at the end to color the cell my favorite shade of yellow.

    However, if you're problem is looping through the sheets, then please repost.

  • Hi bt,

    No I have not, according to Dave Hawley in "MS Excel Visual Basic Examples" that syntax can return errors. He suggest dropping the 'Worksheets'.

    Actually the code that I displayed works fines, I simply need it to do more. In it's present state it highlights "the maximum value" in the column. I need it to hightlight each maximum sale value for each Agent on a monthly basis.


  • Hi Denis,

    The code I dsplayed works fine. I need help modifying it to yield the following results: agents 1 thru agents 100 are all in column 9, the date of sale is in column 10, and the amount sold is in column 12. I want to highlight all the maximum sales values for each agent. Whereas, the code that I displayed is highlighting one singular maximum value.

    Looping will be required, but how do I start?

    Again, any help at all will be greatly appreciated.


  • I've knocked up some code that really isn't the prettiest thing in the world or the fastest, but it does what you ask for. However, I have assumed that you have not used "Autofilter" on this sheet. If you have, do not run this code.

    Here is the code:

  • Just an observation, but it appears that setting conditional formatting of columns I-L to

    Formula is =$L1=MAX($L:$L)
    (format pattern to yellow)

    would achieve the same result.

  • I tested it and it will format any column with this conditional formatting.

    The $L in the formula means it will check the condition of the cell in row L no matter what column the formatting is in.

    Will post a sample if you want!

  • It doesn't do the same thing. That's what I thought. It does what the OP is trying to correct.

    There is more than one agent whereas in your spreadsheet "blaa" is the only agent. In the example I've attached, I've added the agent "Denis" with a value of 200.

    What the OP wants to highlight the max for "blaa" and the max for "Denis". Not just the max in the column.

  • Ok I see your point, I was under the impression that it was only reqiured to highlight the row with the absolute maximum value in row L.

    This will only work if the values for each agent are in a separate column.

    I stand corrected!!!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!