Highlight Top 2 and bottom 2 values in a dynamic range

  • Hi,
    Hoping for some help please.
    I am trying to use some VBA based on that posted here:
    https://www.ozgrid.com/forum/forum/h...288#post905288


    I've been bashing my head trying to get my VBA to work.
    I have data which is grouped by date/time. Each group of date/time data has several columns of numerical values that I need to colour code (red to green) but I also need to highlight the top 2 and bottom 2 values e.g. with a Black border or change the text colour or something similar. I have found out how to colour code the data (from searching various forums) but when I try to highlight the top/bottom 2 using my code based on the code by https://www.ozgrid.com/forum/member/75013-yegarboy I keep getting errors.


    Here's what I've got [INDENT]

    [/INDENT]




    My full code with the Red to Green colour-coding is [INDENT]

    [/INDENT]




    I get the error message "Run-time ..1004. Unable to get the Large property of the WSFn class"


    And the VBA highlights this section:
    "If zCell = Application.WorksheetFunction.Large(rng, 1) Then"


    NOTE: The code seems to run through once with k=10 as the some of the first date/time data gets colour-coded and some data also appears to get highlighted in bright green as shown in picture:
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"DB.jpg","data-attachmentid":1222915}[/ATTACH]


    This pic is not how I want it to appear. I am just including it to show you what happens up to the error.


    I'm no pro. Most of my code is cut and paste from forums so it may not be as elegant as it could be.
    I'd be grateful for any suggestions as to why I get errors and also for any more elegant solutions to my code.
    Thanks for taking the time to help.


  • Thanks for the reply. Unfortunately, I need to use macros as conditional formatting is a pain to do with my data ranges which chang daily.

  • Thanks for the reply. Unfortunately, I need to use macros as conditional formatting is a pain to do with my data ranges which chang daily.


    Hello,


    If your data ranges do change every day ... you can use named ranges defined dynamically ...


    see Debra's excellent explanation :


    https://contextures.com/xlNames01.html#Dynamic

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • see Debra's excellent explanation :


    https://contextures.com/xlNames01.html#Dynamic


    Thanks for your reply. On first glance it looks like Debra's explanation is ok for one table with ranges set up for one set of rows.
    I'll have a deeper look at it but my problem is... I pull the raw data, into one table, from ACCESS every day, I then need to create all the ranges based on the Time of each race (so my WSheet could have around 40 ranges). So, I need macros to set up all the ranges.
    I think I'm reliant on macros though.

  • Caprice,


    Do not know how familiar you are with Excel ...


    You can indeed have a macro to create your 40 + named ranges ...


    But once these dynamic ranges are created ... it is once for all ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You are welcome ...:wink:


    The very same page contains examples about building Offset formulas for your various named ranges ... as well as a sample macro to create dynamic named ranges with code ... :wink:


    Hope this will help you out

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)