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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

bugmonsta

Board Regular
Joined
Dec 17, 2013
Messages
214
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
 

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120
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
 

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
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.
 

pvchick92

New Member
Joined
Feb 25, 2016
Messages
2
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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
Top