zakasnak
Active Member
- Joined
- Sep 21, 2005
- Messages
- 308
- Office Version
- 365
- 2019
- Platform
- Windows
- MacOS
I have a macro that I use daily, however the data that pulls back is not consistently in the same rows, so the formatting needs to be fixed daily.
Is there a way in VBA to use variable rows depending on what's filtered?
Here's part of my formatting code:
Any help would be appreciated, if it's possible.
Is there a way in VBA to use variable rows depending on what's filtered?
Here's part of my formatting code:
Code:
Sub HyperionTeamsC()
Sheets(Array("_books", "_ce", "_games", "_movies", "_music", "_trends", "_goship", "_9301")).Select
Columns("J:K").Select
Selection.ColumnWidth = 9
Sheets("_trends").Activate
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
'- BOOKS
Rows("6:32").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("27:27,31:31").Select
Selection.Interior.ColorIndex = 36
Rows("32:32").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- CE
Rows("12:36").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("18:18,25:25,35:35").Select
Selection.Interior.ColorIndex = 36
Rows("36:36").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- GAMES
Rows("5:45").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("36:36,44:44").Select
Selection.Interior.ColorIndex = 36
Rows("45:45").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- MOVIES
Rows("14:55").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("36:36,54:54").Select
Selection.Interior.ColorIndex = 36
Rows("55:55").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- MUSIC
Rows("26:36").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Rows("35:35").Select
Selection.Interior.ColorIndex = 36
Rows("36:36").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- TRENDS
Rows("7:68").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("12:12,20:20,26:26,67:67").Select
Selection.Interior.ColorIndex = 36
Rows("68:68").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- goship
Rows("4:139").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("20:20,28:28,38:38,66:66,87:87,104:104,111:111,119:119,123:123,133:133,138:138").Select
Selection.Interior.ColorIndex = 36
Rows("139:139").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
ActiveSheet.Next.Select
'- 9301
Rows("4:46").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("5:5,9:9,17:17,20:20,23:23,26:26,30:30,45:45").Select
Selection.Interior.ColorIndex = 36
Rows("46:46").Select
Selection.Interior.ColorIndex = 35
ActiveSheet.ShowAllData
Range("C2").Select
End Sub
Any help would be appreciated, if it's possible.