+ Reply to Thread
Results 1 to 15 of 15

VBA Highlight Max Numbers

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Highlight Max Numbers

    Good morning, I wonder whether someone can help me please.

    I'm trying to put a script together which will highlight the highest and second highlight figures in a given column.

    The sheets I'd like to check these for are called "Direct Activities", "Enhancements", "Indirect Activities", "Overheads" and "Projects".

    For each sheet, I'd like to check each column in the range C:N independently i.e. I need to highlight the pertinent values in column C, then column D etc.

    I've done quite a bit of research and found the link below.

    http://www.mrexcel.com/forum/excel-q...ax-values.html

    and I've put together the following code which deals with sheets and dynamic ranges (below), but I'm a little unsure about how to bring together the two.

    Please Login or Register  to view this content.
    I just wondered whether someone may be able to look at this please and offer some guidance about how I may go about acheiving this.

    Many thanks and kind regards

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: VBA Highlight Max Numbers

    Hi hobbiton73,

    in between "Then" and "End if" put the following bit of code.
    It will highlight the highest number in red and the second highest number in blue.
    Please Login or Register  to view this content.
    Please remember to click the * below if this helps
    Please click the * below if this helps

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @JasperD, thank you very much for taking the time to reply and post a soluition.

    Could you pssible tell me please how I may change this so that, the script starts checking at row 5 because it is currently starting to check from row 4 which is the header row.

    If it helps I've provided the full script below:

    Please Login or Register  to view this content.
    Many thanks and kind regards

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: VBA Highlight Max Numbers

    Hi hobbiton73,

    try this :

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @JasperD, thank you for coming back to me so quickly with this.

    I've tried your suggestion, but unfortunatly I'm receiving the following error:

    Run time error:'1004' Unable to get the Large property of the WorksheetFunction class
    Debug highglights this row as the problem:
    Please Login or Register  to view this content.
    I'm sorry to be a real pain!

    Many thanks and kind regards

  6. #6
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: VBA Highlight Max Numbers

    Can you upload a sample workbook? In my simple test environment it worked fine.

  7. #7
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @JasperD, thank you for coming back to me with this.

    Unfortunately I can't because it contains sensitive information.

    Don't worry about. I'll work at it a bit more and see whether I can get this to work.

    Many thanks and kind regards
    Last edited by hobbiton73; 08-27-2013 at 06:11 AM.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: VBA Highlight Max Numbers

    Any of the values in the range being tested contain Error values (#N/A or #DIV/0!)
    Cheers
    Andy
    www.andypope.info

  9. #9
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @Andy, thank you very much for taking the time to reply to my post.

    I've looked through the raw data and I can confirm that there are no cells in the range which contain errors or are #N/A or #DIV/0!.

    It's a little confusing, because when @JasperD kindly provided the initial code, this did work, but as I posted earlier this was on the header row.

    Many thanks and kind regards
    Last edited by hobbiton73; 08-27-2013 at 07:17 AM.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: VBA Highlight Max Numbers

    Try adding the sheet reference to the ranges.

    Please Login or Register  to view this content.

    If no difference then as JasperD suggested you will need to post an example workbook where the error is present. replace real data with dummy data but ensure the problem is still present.

  11. #11
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @AndyPope, I was just making the very same changes, when your post came through.

    Using your combined replies, I can run the code without any error. The script correctly highlghts the second highest value in each column, but it is highlighting the highest figure as being the column header in row 4.

    Many thanks and kind regards

  12. #12
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @Andy Pope, I'm very sorry to trouble you with this again.

    I tried the code you kindly provided, and although I can now get the code to run without errors, than you, it is unfortunately including the header row as the first row to check.

    I've attached a file that yourself and JasperD requested which illustrates this.

    I just wondered whether you may be able to look at this please.

    Thank you very much and kind regards
    Attached Files Attached Files

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: VBA Highlight Max Numbers

    Please Login or Register  to view this content.
    If the score for 1st and 2nd is a tie the red font will be over written

  14. #14
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi @Andy Pope, thank you very much for taking the time to reply to my post and for putting the solution together.

    Could you possibly tell me please how I may set the 'LastRow' -3, because I'm wanting to include 3 'Totals' rows at the end of my range.

    I'm really very sorry to mess you around with this.

    Many thanks and kind regards

  15. #15
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Highlight Max Numbers

    Hi All,

    I just wanted to let you know that I was fortunate to receive a fully working solution from @AlphaFrog on 'Mr Excel.com' as follows:

    Please Login or Register  to view this content.
    I do however want to thank the contributors from this forum which started me along the right path.

    Many thanks and kind regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Highlight numbers in word doc
    By steve61 in forum Word Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2013, 03:05 AM
  2. Highlight duplicate numbers
    By nakata75 in forum Excel General
    Replies: 2
    Last Post: 09-22-2012, 12:31 PM
  3. Colour paired numbers and highlight unmatched numbers
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2010, 11:24 AM
  4. Highlight repeating numbers
    By Amina in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2007, 09:08 PM
  5. highlight duplicate numbers
    By css in forum Excel General
    Replies: 8
    Last Post: 09-16-2005, 05:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1