Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

VBA to Automatically Highlight the Highest Value in a Column

Question

Hi Caleeco

In the dashboard section of a worksheet I have a 10-cell column containing dynamic values. Each cell contains an underlying formula and the values automatically update as data is entered in the worksheet.

Each cell usually has a background fill colour - 220, 235, 245 in RGB - but I then highlight the highest value cell differently - 200, 225, 180.

If it's practicable I'd like to automate this process with VBA event coding so that as the values in the column change, the highest value is automatically highlighted whilst the remaining cells retain the background fill colour.

And I'd like it in place by Christmas!

No - forget that part. There's actually no urgency whatever. If it gets dull and boring during the festive season, the beer's run out and it's too embarrassing to watch the Boxing Day test, then by all means have a play with it, but otherwise keep it for a spare moment when you've got nothing urgent on. All night marathon not needed for this one.

I should mention that the sheet already has event coding, to automate the conversion to upper case of the entries in one column. I can post this coding if need be, to ensure that both code blocks play nicely together.

Try not to let the prospect of a whitewash (another one!) spoil Christmas - trust you have a really great one, and an awesome 2018.

David
(in Oz)

 

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0
7 hours ago, OldFella said:

Hi Caleeco

 

My turn to be a little slow responding. I figured you must have had one almighty post-Christmas hangover so I switched from checking daily to checking weekly! Good to see you're still alive and functional.

 

Yes, the problem remains unresolved so when you get tired of working on the new website, see what you can come up with. As I said in the OP,  it's low priority, it's a cosmetic enhancement to the same spreadsheet you assisted with last year. FYI, the 10-cell column that I referred to in the OP is the range F2:F11

 

I'll be very interested to see the new website. I've made half-hearted attempts in the past to improve my VBA understanding but other things always seem to take priority, the constant evolution of my spreadsheet being the main problem. Maybe your tutorials will entice me to be more diligent!

 

Regards

David

 

Haha Yes still alive and functional! Had a decent Excel/VBA break over xmas, but enjoying getting back into it now :)

 

Good to hear! I'm currently writing a course to teach VBA from the ground up, with some example code for more advanced tricks! Will let you know when its up :D

 

In regards to your question, I would suggest using Conditional Formatting to achieve that effect. 

 

  1. Select Cells F2:F11
  2. 'Home' ribbon > Conditional Formatting > New Rule > Format only top or bottom ranked values > Put the number '1' in the boxqsl6e0.jpg
  3. Click Format > Fill > More Colours > Custom Tab > Enter your RGB(200, 225, 180) > OK > OK
  4. Select Cells F2:F11 > Home Ribbon > Fill Colour > More Colours > Custom Tab > Enter RGB (220, 235, 245) > OK

 

That should now dynamically highlight the top value in your data set! I think this is a more robust way you achieve what you want. However, if you still would like a VBA solution, I will post it in my next reply!

 

Thanks

Caleeco

    Share this post


    Link to post
    Share on other sites
    • 0
    On 12/24/2017 at 4:45 AM, OldFella said:

    Hi Caleeco

    In the dashboard section of a worksheet I have a 10-cell column containing dynamic values. Each cell contains an underlying formula and the values automatically update as data is entered in the worksheet.

    Each cell usually has a background fill colour - 220, 235, 245 in RGB - but I then highlight the highest value cell differently - 200, 225, 180.

    If it's practicable I'd like to automate this process with VBA event coding so that as the values in the column change, the highest value is automatically highlighted whilst the remaining cells retain the background fill colour.

    And I'd like it in place by Christmas!

    No - forget that part. There's actually no urgency whatever. If it gets dull and boring during the festive season, the beer's run out and it's too embarrassing to watch the Boxing Day test, then by all means have a play with it, but otherwise keep it for a spare moment when you've got nothing urgent on. All night marathon not needed for this one.

    I should mention that the sheet already has event coding, to automate the conversion to upper case of the entries in one column. I can post this coding if need be, to ensure that both code blocks play nicely together.

    Try not to let the prospect of a whitewash (another one!) spoil Christmas - trust you have a really great one, and an awesome 2018.

    David
    (in Oz)

     

    Hi David, 

     

    Sorry for the delayed reply, hope you had a great xmas! I have been busy working on my VBA tutorial website (this website will become a subsidiary of it soon!). 

     

    Do you still need help with this?

    Let me know and I will code something up for you to try :)

     

    Cheers

    Caleeco

    Share this post


    Link to post
    Share on other sites
    • 0

    Hi Caleeco

     

    My turn to be a little slow responding. I figured you must have had one almighty post-Christmas hangover so I switched from checking daily to checking weekly! Good to see you're still alive and functional.

     

    Yes, the problem remains unresolved so when you get tired of working on the new website, see what you can come up with. As I said in the OP,  it's low priority, it's a cosmetic enhancement to the same spreadsheet you assisted with last year. FYI, the 10-cell column that I referred to in the OP is the range F2:F11

     

    I'll be very interested to see the new website. I've made half-hearted attempts in the past to improve my VBA understanding but other things always seem to take priority, the constant evolution of my spreadsheet being the main problem. Maybe your tutorials will entice me to be more diligent!

     

    Regards

    David

    Share this post


    Link to post
    Share on other sites
    • 0

    As promised, the VBA solution:

    Post this in the sheet code of the relevant sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    '// Source: www.ExcelWTF.com
    '// Purpose: Monitor Sheet Change & Highlight Highest Value(s)
        
        Dim rCells As Range, r As Range
        Set rCells = Range("F2:F11")
        
        For Each r In rCells
            If r.Value = WorksheetFunction.Max(rCells) Then
                r.Interior.Color = RGB(200, 225, 180)
            Else
                r.Interior.Color = RGB(220, 235, 245)
            End If
        Next r
    End Sub

    This will be computationally more expensive than using the conditional formatting in my previous post (as it is monitoring changes in the entire sheet). 

     

    Let me know if that helped

    Thanks

    Caleeco

    Share this post


    Link to post
    Share on other sites
    • 0

    Hi Caleeco

    Re the conditional formatting option - of course! Such an elegantly, simple fix. In all my years of Exceling I've never used that aspect of conditional formatting, so I never even considered it. I use conditional formatting regularly, but always for something either date related or formula based. Brilliant! I've applied it to one worksheet so far and it appears to work perfectly. I'll now look for a sheet where I can add some data that will cause the ranking to change, so I can verify that the colours change correctly. I'll get back to you if I have any problem.

    Given the elegance of that solution I'll skip VBA. I also don't need additional drain on computation resources, I've already got enough on the spreadsheet that gives a noticeable drop in cooling fan speed!

    Job done - now you can go back to your website.

    Many thanks

    David

    • Like 1

    Share this post


    Link to post
    Share on other sites
    • 0
    15 minutes ago, OldFella said:

    Hi Caleeco

    Re the conditional formatting option - of course! Such an elegantly, simple fix. In all my years of Exceling I've never used that aspect of conditional formatting, so I never even considered it. I use conditional formatting regularly, but always for something either date related or formula based. Brilliant! I've applied it to one worksheet so far and it appears to work perfectly. I'll now look for a sheet where I can add some data that will cause the ranking to change, so I can verify that the colours change correctly. I'll get back to you if I have any problem.

    Given the elegance of that solution I'll skip VBA. I also don't need additional drain on computation resources, I've already got enough on the spreadsheet that gives a noticeable drop in cooling fan speed!

    Job done - now you can go back to your website.

    Many thanks

    David

     

    Hi David, 

     

    Glad you liked the CF solution! Yes, with the spreadsheets I build; I always go for simplicity. As much as I enjoy writing code, VBA isn't always the best answer :) Excel has lots of in-built tools we can take advantage of! CF is very robust, so many things you can use it for! 

     

    Ok sounds good, let me know if the colours don't change correctly (I did some testing on my sheet which seemed to work). 

     

    Haha, yes when dealing with big spreadsheets... efficiency savings are key! Glad I could help

     

    Caleeco

     

    • Thanks 1

    Share this post


    Link to post
    Share on other sites

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

    ×