highlighted rows

paquirl

paquirl

Board Regular
Joined
Oct 12, 2010
Messages
226
Office Version
  1. 2016
Platform
  1. Windows
after highlighting certain rows on a large spreadsheet, is it possible to make it view only the highlighted rows?
 
Sort by date Sort by votes
paquirl said:
i do have a large spreadsheet, but i've tried it again waiting a long time and still nothing. the previous code that worked flashed the screen a lot and took several seconds. i've tried this new one a few times waiting up to three minutes.
Click to expand...

My suggestion would be too go with the previous code then. I don't have an answer for the flashing. There is nothing in the code that I see that would cause that.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Ok, i figured out it was not working when i use the keyboard shortcut, but the new code actually does work without screen flashing and works pretty fast if i manually run the macro!

I have one other issue. The macro you just helped with is the second macro I run on this spreadsheet. The first one highlights certain rows based on filtering. I set an autofilter and filter by criteria in one column and mark the affected rows by highlighting them, then turn it off and filter by criteria in another column and highlight those rows, then turn it off and filter by a third column and highlight those rows. When I turn off the last filter i then run the macro you wrote and it shows all the highlighted rows.

The problem is, the first macro is missing some rows.

Sub ABSOLUTEHIGHLIGHTED()
'
' ABSOLUTEHIGHLIGHTED Macro
' Macro recorded 3/10/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Rows("4:4").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=7, Criteria1:=">=5 to 6 weeks", Operator:= _
xlAnd
Rows("5:386").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=7
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Selection.AutoFilter Field:=25, Criteria1:=">(35) Active", Operator:= _
xlAnd
Rows("405:1981").Select
Range("T405").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=25
Selection.AutoFilter Field:=38, Criteria1:="Yes"
Rows("1026:2098").Select
Range("T1026").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=38
End Sub
 
Upvote 0
paquirl said:
Ok, i figured out it was not working when i use the keyboard shortcut, but the new code actually does work without screen flashing and works pretty fast if i manually run the macro!

I have one other issue. The macro you just helped with is the second macro I run on this spreadsheet. The first one highlights certain rows based on filtering. I set an autofilter and filter by criteria in one column and mark the affected rows by highlighting them, then turn it off and filter by criteria in another column and highlight those rows, then turn it off and filter by a third column and highlight those rows. When I turn off the last filter i then run the macro you wrote and it shows all the highlighted rows.

The problem is, the first macro is missing some rows.

Sub ABSOLUTEHIGHLIGHTED()
'
' ABSOLUTEHIGHLIGHTED Macro
' Macro recorded 3/10/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Rows("4:4").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=7, Criteria1:=">=5 to 6 weeks", Operator:= _
xlAnd
Rows("5:386").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=7
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Selection.AutoFilter Field:=25, Criteria1:=">(35) Active", Operator:= _
xlAnd
Rows("405:1981").Select
Range("T405").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=25
Selection.AutoFilter Field:=38, Criteria1:="Yes"
Rows("1026:2098").Select
Range("T1026").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=38
End Sub
Click to expand...

Does this help?
Code: 
Sub paquirl3()
' ABSOLUTEHIGHLIGHTED Macro
' Macro recorded 3/10/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Rows("4:4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">=5 to 6 weeks", Operator:= _
xlAnd
Range("A5:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A5:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=25, Criteria1:=">(35) Active", Operator:= _
xlAnd
Range("A405:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A405:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=25
Selection.AutoFilter Field:=38, Criteria1:="Yes"
Range("A1026:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A1026:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=38

End Sub
 
Upvote 0
your version seems to do the same thing. there are lot of rows with "yes" in the Inventory column that didnt get highlighted.
 
Upvote 0
unfortunately that doesn't work either. :( please continue to help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
How do you highlight an entire row if all values are the same
Replies
4
Views
140
Peter_SSs
Peter_SSs
R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
316
etaf
etaf
D
  • Solved
VBA Code To Highlight Row And Column Where Cursor Is
2
Replies
14
Views
1K
smozgur
smozgur
E
  • Solved
VBA -highlighting rows when a condition is met
Replies
2
Views
190
Evie76
E
G
  • Solved
Search Keywords and Highlight in different colors
Replies
9
Views
381
gibson8
G

Forum statistics

Threads
1,215,219
Messages
6,123,692
Members
449,117
Latest member
Aaagu

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