VBA Highlight Highest & Second Highest Value In Each Column

ir121973 · Aug 28th 2013

Hi, I wonder whether someone could possibly help me please.

I'm trying to put together a script which will highlight the highest and second highest value for each column in a given range for multiple sheets.

I've started the script off below, which I think correctly creates the array of sheets and the starting row of each column.

Code

Sub Highlight()


    Dim ws As Worksheet, Worksheet, LastRow As Long
    Const StartRow As Long = 5
    
    Application.ScreenUpdating = False
        
        For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
            LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
             If LastRow >= StartRow Then






            End If
            ws.Columns("B:Q").AutoFit
            
        Next ws
     Application.ScreenUpdating = True
    End Sub

Display More

Now I did try to get some help here: http://www.excelforum.com/exce…ighlight-max-numbers.html but as you will see I seem to have hit a bit of brick wall.

I just wondered whether someone could possibly look at this please, and offer some guidance on how I may go about achieiving this.

Thank you and kind regards

yegarboy · Aug 28th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

This should work, I changed your sheet names for testing purposes.

Code

Sub Extract()


On Error Resume Next


    
Dim zCell As Range
Dim rng As Range
Dim lCol As Long
Dim lLastRow As Long
Dim ws As Worksheet


      
 For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3")) 'Change Sheet Names To Suit
 
      With ws
             For lCol = 2 To 3 'Change Columns To Look At. 2 = "B"  3 = "C"
      
             lLastRow = .Cells(.Rows.Count, lCol).End(xlUp).Row
             Set rng = .Range(.Cells(1, lCol), .Cells(lLastRow, lCol))
      


               
                     For Each zCell In rng
      
                     If zCell = Application.WorksheetFunction.Large(rng, 1) Then
                        zCell.Interior.Color = vbGreen  'Color for Highest Value
                        GoTo NC
                     Else
                        zCell.Interior.ColorIndex = xlNone
                     End If
        
                     If zCell = Application.WorksheetFunction.Large(rng, 2) Then
                        zCell.Interior.Color = vbRed   'Color for 2nd Highest Value
                        GoTo NC
                        Else
                        zCell.Interior.ColorIndex = xlNone
                     End If
                  
                    ws.Columns("B:D").AutoFit  'Change Columns to AutoFit
      
        
NC:
        
                    Next
     
             Next lCol
    
       End With
       
Next ws




End Sub

Display More

ir121973 · Aug 28th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

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

The code works, but it starts searching at row 4 which is the header row. Could you possibly tell me please how I may be able to make the change to start at row 5.

Many thanks and kind regards

yegarboy · Aug 28th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

This line contains the Starting Row. Change the 1 to a 5 like this and you should be good to go.

Code

Set rng = .Range(.Cells(5, lCol), .Cells(lLastRow, lCol))

ir121973 · Aug 28th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

Hi yegarboy, that's great. Thank you very much for your time and trouble.

Kind Regards

Smallman · Aug 28th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

Hi

I was looking at this post yesterday, prepared a file and someone came over, disturbed me and took me away. I did not get back to my desk for hours and forgot to post a reply. Sorry if this is old now but I think this method has merrit.

I used Conditional formatting to achieve the highlighting of the first and second ranked figures in a column. File attached to prove workings.

Take care

Smallman

ir121973 · Aug 29th 2013

Re: VBA Highlight Highest & Second Highest Value In Each Column

Hi Smallman, thank you for taking the time to reply to my post and for the solution. It is greatly appreciated.

Many thanks and kind regards

VBA Highlight Highest & Second Highest Value In Each Column

Participate now!

Share