VBA - Highlighting entire row based on top values in column

pvchick92

New Member
Joined
Feb 25, 2016
Messages
2
Hi everyone,
I'm learning VBA and am very unsure of what I am doing! I have columns A:K and need to highlight entire rows based on the top 150 values in column F. I know that there is conditional formatting for top values, but I can't seem to find anything that will highlight the entire row rather than a single cell.

Thank you in advance!!!! <3
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi there,

I have a project in the works which I have added an automated row highlight which I hope may be able to help you out.

I am just using dates but you can add your own conditions to this to see if it works.
The code below will only highlight the row up to the last column that contains data.

The code looks at column J for dates, and if they match the criteria it will highlight the row, note I am using RGB colours rather than VBcolours as in testing my users found that a lot of VBcolours on screen quite harsh to look at!

Code:
Private Sub HighlightRows()
    Dim c As Range
    Dim lastcol As Integer
    Dim lastrow As Long
    With ActiveSheet
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
        lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For Each c In Range("j2:j" & lastrow)
If c.Value < Date Then [COLOR="#008000"]'if date is older than today highlight yellow[/COLOR]    
Range(Cells(c.Row, 1), Cells(c.Row, lastcol)).Interior.Color = RGB(255, 255, 179)

ElseIf c.Value = Date Or c.Value = Date + 1 Or c.Value = Date + 2 Then [COLOR="#008000"]'if it is within the next 2 days highlight in red[/COLOR]
    Range(Cells(c.Row, 1), Cells(c.Row, lastcol)).Interior.Color = RGB(255, 64, 64)

ElseIf c.Value > Date + 3 And c.Value < Date + 14 Then [COLOR="#008000"]'if date is > than 1 week and less that 2 weeks, orange[/COLOR]    
Range(Cells(c.Row, 1), Cells(c.Row, lastcol)).Interior.Color = RGB(255, 184, 149)

ElseIf c.Value >= Date + 14 And c.Value <= Date + 21 Then [COLOR="#008000"]'if cell value is within 14 and 21 days = set to green[/COLOR]
    Range(Cells(c.Row, 1), Cells(c.Row, lastcol)).Interior.Color = RGB(204, 255, 204)

ElseIf c.Value >= Date + 22 Then [COLOR="#008000"]'if cell value is > 21 days set it to nothing[/COLOR]
Range(Cells(c.Row, 1), Cells(c.Row, lastcol)).Interior.Color = -4142
End If
    Next c
End Sub
 
Upvote 0
Code:
Sub color()Dim afw As Object
Set afw = Application.WorksheetFunction
For i = 1 To 150
    t = afw.Large(Range("f:F"), i)
    k = afw.Match(t, Range("f:F"), 0)
Range("A" & k & ":K" & k).Interior.color = vbRed
Next
End Sub
 
Upvote 0
Hi everyone,
I'm learning VBA and am very unsure of what I am doing! I have columns A:K and need to highlight entire rows based on the top 150 values in column F. I know that there is conditional formatting for top values, but I can't seem to find anything that will highlight the entire row rather than a single cell.

Thank you in advance!!!! <3


You could do it easily without resorting to VBA.
Add a formula to a single cell, let's say M1:

=LARGE($F$2:$F$3000,150)

which will return the 150th largest number in column F (define the size of the range as needed)

Then add a Conditional Format:

=$K1>=$M$1

and apply it to your entire range, $A$2:$K$3000. Make sure your conditional rule doesn't change the $K1 to $K$1 automatically.
I'm not sure if these figures will give you exactly the top 150, you may need to adjust the >= to > in the formatting rule perhaps, but that's the basic gist of how you can do it.
 
Upvote 0
This was exactly what I needed! The only problem is that if there are two equal values, it only highlights one of them. Is there a way around that? Thanks so much!!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top