MacroProblem: Highlight (color) Rows that Meet Criteria from a List

cgeorge4

cgeorge4

Board Regular
Joined
Jul 24, 2011
Messages
91
Hello,

<TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 432pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=576 colSpan=9>I frequently use a macro that highlights any row that meets the following criteria:

<TABLE style="WIDTH: 672pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=896><COLGROUP><COL style="WIDTH: 48pt" span=14 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 672pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=896 colSpan=14>If the data in column B - of any row in my spreadsheet - equals the number entered in cell A1
- then hightlight the whole row yellow

</TD></TR></TBODY></TABLE>

</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 816pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1088><COLGROUP><COL style="WIDTH: 48pt" span=17 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 816pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=1088 colSpan=17>I would like to edit my code so that it still highlights the rows that meet the current criteria - but I would like to turn the one cell reference (cell A1) into a range.</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 192pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=256 colSpan=4>Here are my worksheet specifics:</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=704><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 528pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=704 colSpan=11>1) Data on this spreadsheet is a download from the GL and has data from column A to T (starting on row 2)</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 480pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=640 colSpan=10>2) Data in column B of all rows is a 6 digit number that always starts with a zero (ex: 017864)</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 432pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=576 colSpan=9>3) This spreadsheet could have 65,000 rows to close to the max that Excel provides</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=832><COLGROUP><COL style="WIDTH: 48pt" span=13 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 624pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=832 colSpan=13>4) IMPORTANT!......The "referencing range" will be placed in columns U, V, W, and X on the same
sheet (starting on row 2).

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 480pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=640 colSpan=10>5) The data entered in columns U to X will have 6 digits and will always start with a zero.</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 384pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=512 colSpan=8>6) Data entered in columns U to X could be up to any number of rows long.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=128 colSpan=2>Special Request:</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 1344pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1792><COLGROUP><COL style="WIDTH: 48pt" span=28 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 1344pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=1792 colSpan=28>If I click (select) column U (for example), I would like the rows on my worksheet that meet the criteria from that
column to be highlighted a certain color,......and if I click on column W, then those rows that
meet this new criteria to be highlighted a different color.

</TD></TR></TBODY></TABLE>

</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 384pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=512 colSpan=8>The different colors are not important - any color will do except red.</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 240pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=320 colSpan=5>I should end up with rows that are highlighted either of the 4 colors.</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl65 height=20 width=128 colSpan=2>Here is my code:</TD></TR></TBODY></TABLE>Sub Highlight_Specific_Row()
Dim rFound As Range
Dim lFirstRow As Long

With Range("B:B")
If [A1] = "" Then Exit Sub
Set rFound = .Find([A1], LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 6
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End With

End Sub

Thank you so much,
Juicy
 
Sort by date Sort by votes

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

May be try the code below:

Code: 
Sub Highlight_Specific_Row()
Dim rFound As Range
Dim lFirstRow As Long
[COLOR=red]Dim my_range As Range[/COLOR]
 
Set my_range = Range("U2:W50") '--> Change to suit

With Range("B:B")
    For Each r In my_range
        If r.Value <> "" Then
            Set rFound = .Find(r.Value, LookIn:=xlValues)
            If Not rFound Is Nothing Then
                lFirstRow = rFound.Row
                Do
                    Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 6
                    Set rFound = .FindNext(rFound)
                Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
            End If
        End If
    Next
End With

End Sub
 
Upvote 0
Good evening,

Well,.....it finally works.;)


What I did is take the code that you modified for me and copied it 3 times - then just changed the range and the color index for each.

I didn't need the 'private sub' code - so I removed it

I also setup a macro toolbar showing all 4 macros seperately.


I'm going to go ahead and show the final code in full - in case someone else might find it useful.

Sub DoRowColor1()
Dim rFound As Range
Dim lFirstRow As Long
Dim my_range As Range

Set my_range = Range("U2:u50") '--> Change to suit
With Range("B:B")
For Each r In my_range
If r.Value <> "" Then
Set rFound = .Find(r.Value, LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 20
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End If
Next
End With
End Sub
'-------------------------------------------------------------------------------------------
Sub DoRowColor2()
Dim rFound As Range
Dim lFirstRow As Long
Dim my_range As Range

Set my_range = Range("v2:v50") '--> Change to suit
With Range("B:B")
For Each r In my_range
If r.Value <> "" Then
Set rFound = .Find(r.Value, LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 36
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End If
Next
End With
End Sub
'-------------------------------------------------------------------------------------
Sub DoRowColor3()
Dim rFound As Range
Dim lFirstRow As Long
Dim my_range As Range

Set my_range = Range("w2:w50") '--> Change to suit
With Range("B:B")
For Each r In my_range
If r.Value <> "" Then
Set rFound = .Find(r.Value, LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 40
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End If
Next
End With
End Sub
'-------------------------------------------------------------------------------------------
Sub DoRowColor4()
Dim rFound As Range
Dim lFirstRow As Long
Dim my_range As Range

Set my_range = Range("x2:x50") '--> Change to suit
With Range("B:B")
For Each r In my_range
If r.Value <> "" Then
Set rFound = .Find(r.Value, LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 24
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End If
Next
End With
End Sub



Thanks for getting me 100%!
Mwaaaaaa......

Juicy,
 
Upvote 0
Hi,

Ignore my code below:
I have missed out your color.

Code: 
Sub Highlight_Specific_Row()
Dim rFound As Range
Dim lFirstRow As Long
Dim my_range As Range
 
Set my_range = [COLOR=red]Union(Range("U2:W50"), Range("v2:v50"), Range("w2:w50"), Range("x2:x50"))[/COLOR] '--> Change to suit
 
With Range("B:B")
    For Each r In my_range
        If r.Value <> "" Then
            Set rFound = .Find(r.Value, LookIn:=xlValues)
            If Not rFound Is Nothing Then
                lFirstRow = rFound.Row
                Do
                    Rows(rFound.Row).Columns("A:T").Interior.ColorIndex = 6
                    Set rFound = .FindNext(rFound)
                Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
            End If
        End If
    Next
End With
End Sub
 
Last edited:
Upvote 0
Hi,
Thanks anyways.

I am 100% with the code I have. You're sweet.

Thanks for helping.

Have a great week.

Juicy
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
624
epactheactor
E
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
865
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
365
esben
E

Forum statistics

Threads
1,216,101
Messages
6,128,835
Members
449,471
Latest member
lachbee

Share this page

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