VBA Variable Row Formatting

[zakasnak]

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:

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.

 

[zakasnak]

I've gotten this to work perfectly on all of the sheets in my workbook!

All I need is the piece of code that would bold the used range with "Total" in Col C (or D, depending on the sheet).

Can anyone help?

 

[jasonb75]

Not tested, try

For Each tCell In .Range("C1", "D" & i)

 

[zakasnak]

Not tested, try

For Each tCell In .Range("C1", "D" & i)

Jason,

I currently have this code in what you've previously helped me with:

For Each tCell In .Range("C1", "C" & i)

Am I changing this line? or adding this in a different place?

 

[jasonb75]

Change the existing line, from what you've said it looks like it should work.