VBA Variable Row Formatting

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
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:

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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,148
Office Version
2019
Platform
Windows
It would seem that you have a lot of excess code, is there a consistant criteria in all sheets to determine which have bold text, and which have fill colorindex 40, 36, and 35 respectively?
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
When this part of the code runs, I've filtered by "Total" in Col D.

The "Merchandising Total" is copied from another sheet & is not included in the filter.

Here's a sample sheet:

_books


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Microsoft Sans Serif,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 155px"><COL style="WIDTH: 31px"><COL style="WIDTH: 245px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Prod Dept</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">PC</TD><TD style="FONT-WEIGHT: bold">Description</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>BOOKS BARGAIN</TD><TD style="TEXT-ALIGN: center">60</TD><TD>BOOKS VALUE PROMOTIONAL N/A</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>BOOKS BARGAIN</TD><TD style="TEXT-ALIGN: center">61</TD><TD>BOOKS VALUE HURT BOOKS N/A</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>BOOKS BARGAIN</TD><TD style="TEXT-ALIGN: center">62</TD><TD>BOOKS VALUE PROMOTIONAL N/A</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>BOOKS BARGAIN</TD><TD style="TEXT-ALIGN: center">63</TD><TD>BOOKS VALUE PROMOTIONAL SHELF N/A</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS BARGAIN Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">3</TD><TD>BOOKS NEW CD-I FRONT-LINE</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">50</TD><TD>BOOKS NEW MASS MARKET FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">51</TD><TD>BOOKS NEW HARDBACK BOOKS FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">53</TD><TD>BOOKS NEW CHILDRENS BOOK FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">54</TD><TD>BOOKS NEW CALENDARS FRONT-LINE</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">55</TD><TD>BOOKS NEW TRADE PAPERBACK FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">56</TD><TD>BOOKS NEW MAPS FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">59</TD><TD>BOOKS NEW BOOKS ON CASSETTE N/A</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">95</TD><TD>BOOKS NEW TEXTBOOKS FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">100</TD><TD>BOOKS NEW BOOKS ON CD FRONT-LIST</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>BOOKS NEW</TD><TD style="TEXT-ALIGN: center">655</TD><TD>BOOKS NEW TRADE PAPERBACK MARKED DOWN</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS NEW Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">94</TD><TD>BOOKS USED TEXTBOOKS USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">150</TD><TD>BOOKS USED MASS MARKET USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">151</TD><TD>BOOKS USED HARDBACK BOOKS USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">153</TD><TD>BOOKS USED CHILDRENS BOOK USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">155</TD><TD>BOOKS USED TRADE PAPERBACK USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">166</TD><TD>BOOKS USED PROMOTIONAL SHELF USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>BOOKS USED</TD><TD style="TEXT-ALIGN: center">167</TD><TD>BOOKS USED BOOKS ON CD USED</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS USED Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99">BOOKS Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD>NEWS STAND</TD><TD style="TEXT-ALIGN: center">96</TD><TD>NEWS STAND PERIODICALS NEWSPAPERS</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD>NEWS STAND</TD><TD style="TEXT-ALIGN: center">97</TD><TD>NEWS STAND MAGAZINES MAGAZINES</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">NEWS STAND Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99">NEWSSTAND Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc">DEPARTMENT Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc"> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff">MERCHANDISING Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff"> </TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=B1</TD></TR><TR><TD>C2</TD><TD>=IF(B2="total",(CONCATENATE(A2," ",B2)),(IF(D2="total",(CONCATENATE(B2," ",D2)),B2)))</TD></TR><TR><TD>C3</TD><TD>=IF(B3="total",(CONCATENATE(A3," ",B3)),(IF(D3="total",(CONCATENATE(B3," ",D3)),B3)))</TD></TR><TR><TD>C4</TD><TD>=IF(B4="total",(CONCATENATE(A4," ",B4)),(IF(D4="total",(CONCATENATE(B4," ",D4)),B4)))</TD></TR><TR><TD>C5</TD><TD>=IF(B5="total",(CONCATENATE(A5," ",B5)),(IF(D5="total",(CONCATENATE(B5," ",D5)),B5)))</TD></TR><TR><TD>C6</TD><TD>=IF(B6="total",(CONCATENATE(A6," ",B6)),(IF(D6="total",(CONCATENATE(B6," ",D6)),B6)))</TD></TR><TR><TD>C7</TD><TD>=IF(B7="total",(CONCATENATE(A7," ",B7)),(IF(D7="total",(CONCATENATE(B7," ",D7)),B7)))</TD></TR><TR><TD>C8</TD><TD>=IF(B8="total",(CONCATENATE(A8," ",B8)),(IF(D8="total",(CONCATENATE(B8," ",D8)),B8)))</TD></TR><TR><TD>C9</TD><TD>=IF(B9="total",(CONCATENATE(A9," ",B9)),(IF(D9="total",(CONCATENATE(B9," ",D9)),B9)))</TD></TR><TR><TD>C10</TD><TD>=IF(B10="total",(CONCATENATE(A10," ",B10)),(IF(D10="total",(CONCATENATE(B10," ",D10)),B10)))</TD></TR><TR><TD>C11</TD><TD>=IF(B11="total",(CONCATENATE(A11," ",B11)),(IF(D11="total",(CONCATENATE(B11," ",D11)),B11)))</TD></TR><TR><TD>C12</TD><TD>=IF(B12="total",(CONCATENATE(A12," ",B12)),(IF(D12="total",(CONCATENATE(B12," ",D12)),B12)))</TD></TR><TR><TD>C13</TD><TD>=IF(B13="total",(CONCATENATE(A13," ",B13)),(IF(D13="total",(CONCATENATE(B13," ",D13)),B13)))</TD></TR><TR><TD>C14</TD><TD>=IF(B14="total",(CONCATENATE(A14," ",B14)),(IF(D14="total",(CONCATENATE(B14," ",D14)),B14)))</TD></TR><TR><TD>C15</TD><TD>=IF(B15="total",(CONCATENATE(A15," ",B15)),(IF(D15="total",(CONCATENATE(B15," ",D15)),B15)))</TD></TR><TR><TD>C16</TD><TD>=IF(B16="total",(CONCATENATE(A16," ",B16)),(IF(D16="total",(CONCATENATE(B16," ",D16)),B16)))</TD></TR><TR><TD>C17</TD><TD>=IF(B17="total",(CONCATENATE(A17," ",B17)),(IF(D17="total",(CONCATENATE(B17," ",D17)),B17)))</TD></TR><TR><TD>C18</TD><TD>=IF(B18="total",(CONCATENATE(A18," ",B18)),(IF(D18="total",(CONCATENATE(B18," ",D18)),B18)))</TD></TR><TR><TD>C19</TD><TD>=IF(B19="total",(CONCATENATE(A19," ",B19)),(IF(D19="total",(CONCATENATE(B19," ",D19)),B19)))</TD></TR><TR><TD>C20</TD><TD>=IF(B20="total",(CONCATENATE(A20," ",B20)),(IF(D20="total",(CONCATENATE(B20," ",D20)),B20)))</TD></TR><TR><TD>C21</TD><TD>=IF(B21="total",(CONCATENATE(A21," ",B21)),(IF(D21="total",(CONCATENATE(B21," ",D21)),B21)))</TD></TR><TR><TD>C22</TD><TD>=IF(B22="total",(CONCATENATE(A22," ",B22)),(IF(D22="total",(CONCATENATE(B22," ",D22)),B22)))</TD></TR><TR><TD>C23</TD><TD>=IF(B23="total",(CONCATENATE(A23," ",B23)),(IF(D23="total",(CONCATENATE(B23," ",D23)),B23)))</TD></TR><TR><TD>C24</TD><TD>=IF(B24="total",(CONCATENATE(A24," ",B24)),(IF(D24="total",(CONCATENATE(B24," ",D24)),B24)))</TD></TR><TR><TD>C25</TD><TD>=IF(B25="total",(CONCATENATE(A25," ",B25)),(IF(D25="total",(CONCATENATE(B25," ",D25)),B25)))</TD></TR><TR><TD>C26</TD><TD>=IF(B26="total",(CONCATENATE(A26," ",B26)),(IF(D26="total",(CONCATENATE(B26," ",D26)),B26)))</TD></TR><TR><TD>C27</TD><TD>=IF(B27="total",(CONCATENATE(A27," ",B27)),(IF(D27="total",(CONCATENATE(B27," ",D27)),B27)))</TD></TR><TR><TD>C28</TD><TD>=IF(B28="total",(CONCATENATE(A28," ",B28)),(IF(D28="total",(CONCATENATE(B28," ",D28)),B28)))</TD></TR><TR><TD>C29</TD><TD>=IF(B29="total",(CONCATENATE(A29," ",B29)),(IF(D29="total",(CONCATENATE(B29," ",D29)),B29)))</TD></TR><TR><TD>C30</TD><TD>=IF(B30="total",(CONCATENATE(A30," ",B30)),(IF(D30="total",(CONCATENATE(B30," ",D30)),B30)))</TD></TR><TR><TD>C31</TD><TD>=IF(B31="total",(CONCATENATE(A31," ",B31)),(IF(D31="total",(CONCATENATE(B31," ",D31)),B31)))</TD></TR><TR><TD>C32</TD><TD>=IF(B32="total",(CONCATENATE(A32," ",B32)),(IF(D32="total",(CONCATENATE(B32," ",D32)),B32)))</TD></TR><TR><TD>C34</TD><TD>=IF(B34="total",(CONCATENATE(A34," ",B34)),(IF(D34="total",(CONCATENATE(B34," ",D34)),B34)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,148
Office Version
2019
Platform
Windows
A visual example doesn't mean a lot without an explaination, does that mean

Totals with a total, colorindex 40
Totals with no total, colorindex 36
Department total, colorindex 35

If not, what do you look at to determine which colorindex to use for each row?

Do you always filter for total on column D before running the code?

Is it column D on every sheet?
 
Last edited:

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
A visual example doesn't mean a lot without an explaination, does that mean

Totals with a total, colorindex 40
Totals with no total, colorindex 36
Department total, colorindex 35

If not, what do you look at to determine which colorindex to use for each row?

Do you always filter for total on column D before running the code?

Is it column D on every sheet?
I think something can be accomplished using the formula in Col C, but I don't know how to do that:
Code:
=IF(B2="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A2," ",B2)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D2="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B2," ",D2)[/COLOR])[/COLOR],B2)[/COLOR])[/COLOR])
Filter "Total" is applied to Col D on every sheet, colorindex 40
If B="Total" then it should be colorindex 36
Department total is the same on each sheet (just variable rows), colorindex 35

Code:
[B]_books[/B]
 
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Microsoft Sans Serif,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 115px"><COL style="WIDTH: 97px"><COL style="WIDTH: 155px"><COL style="WIDTH: 31px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Grp Dept Descr</TD><TD style="FONT-WEIGHT: bold">Prod Dept</TD><TD style="FONT-WEIGHT: bold">Prod Dept</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">PC</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>BOOKS</TD><TD>BOOKS BARGAIN</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS BARGAIN Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>BOOKS</TD><TD>BOOKS NEW</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS NEW Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>BOOKS</TD><TD>BOOKS USED</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">BOOKS USED Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD>BOOKS</TD><TD>Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99">BOOKS Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD>NEWSSTAND</TD><TD>NEWS STAND</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99">NEWS STAND Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>NEWSSTAND</TD><TD>Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99">NEWSSTAND Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD>DEPARTMENT</TD><TD>Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc">DEPARTMENT Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">Total</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD style="COLOR: #ffffff">Keep this row…</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD>MERCHANDISING</TD><TD>Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff">MERCHANDISING Total</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99ccff"></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>[B]Spreadsheet Formulas[/B]</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=B1</TD></TR><TR><TD>C6</TD><TD>=IF(B6="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A6," ",B6)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D6="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B6," ",D6)[/COLOR])[/COLOR],B6)[/COLOR])[/COLOR])</TD></TR><TR><TD>C18</TD><TD>=IF(B18="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A18," ",B18)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D18="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B18," ",D18)[/COLOR])[/COLOR],B18)[/COLOR])[/COLOR])</TD></TR><TR><TD>C26</TD><TD>=IF(B26="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A26," ",B26)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D26="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B26," ",D26)[/COLOR])[/COLOR],B26)[/COLOR])[/COLOR])</TD></TR><TR><TD>C27</TD><TD>=IF(B27="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A27," ",B27)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D27="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B27," ",D27)[/COLOR])[/COLOR],B27)[/COLOR])[/COLOR])</TD></TR><TR><TD>C30</TD><TD>=IF(B30="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A30," ",B30)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D30="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B30," ",D30)[/COLOR])[/COLOR],B30)[/COLOR])[/COLOR])</TD></TR><TR><TD>C31</TD><TD>=IF(B31="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A31," ",B31)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D31="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B31," ",D31)[/COLOR])[/COLOR],B31)[/COLOR])[/COLOR])</TD></TR><TR><TD>C32</TD><TD>=IF(B32="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A32," ",B32)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D32="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B32," ",D32)[/COLOR])[/COLOR],B32)[/COLOR])[/COLOR])</TD></TR><TR><TD>C34</TD><TD>=IF(B34="total",[COLOR=#008000](CONCATENATE[COLOR=#0000ff](A34," ",B34)[/COLOR])[/COLOR],[COLOR=#008000](IF[COLOR=#0000ff](D34="total",[COLOR=#ff0000](CONCATENATE[COLOR=#804000](B34," ",D34)[/COLOR])[/COLOR],B34)[/COLOR])[/COLOR])</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
A visual example doesn't mean a lot without an explaination, does that mean

Totals with a total, colorindex 40 - YES
Totals with no total, colorindex 36 - Depends on if Col B = "Total"
Department total, colorindex 35 - YES, Always

If not, what do you look at to determine which colorindex to use for each row?

Do you always filter for total on column D before running the code?

Is it column D on every sheet?
Help?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,148
Office Version
2019
Platform
Windows
Try this version, filtering is optional, this works on the content of the cells.

Code:
Sub HyperionTeamsC()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Dim wsArray, ws As Long, tCell As Range, ic As Long, i As Long, icol As Long
        wsArray = Array("_books", "_ce", "_games", "_movies", "_music", "_trends", "_goship", "_9301")
    For ws = o To UBound(wsArray)
        With Sheets(wsArray(ws))
             .Columns("J:K").ColumnWidth = 9
             i = .Cells(Rows.Count, "D").End(xlUp).Row
             icol = .UsedRange.Columns.Count
             For Each tCell In .Range("D1", "D" & i)
                With tCell
                    If Application.Proper(.Offset(, -1).Value) = "Department Total" Then
                        ic = 35
                    ElseIf tCell.Row = i Then
                        ic = 37
                    ElseIf Application.Proper(.Offset(, -2).Value) = "Total" Then
                        ic = 36
                    ElseIf Application.Proper(.Value) = "Total" Then
                        ic = 40
                    Else: ic = 0
                    End If
                End With
                With .Range(.Cells(tCell.Row, 1), .Cells(tCell.Row, icol))
                        .Interior.ColorIndex = ic
                        .Font.Bold = (tCell.Row = 1 Or ic > 0)
                End With
            Next
        End With
    Next
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
End Sub
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
This works good! But I want to see the screen updating. I took out
Code:
        .ScreenUpdating = False
Should I just change it to TRUE?

I took out the manual calculation, too, because it was messing with some formulas I had going on with previous parts of the code.

Code:
Sub HyperionTeamsC_new()
    With Application
    End With
    Dim wsArray, ws As Long, tCell As Range, ic As Long, i As Long, icol As Long
        wsArray = Array("_books", "_ce", "_games", "_movies", "_music", "_trends", "_goship", "_9301")
    For ws = o To UBound(wsArray)
        With Sheets(wsArray(ws))
             .Columns("J:K").ColumnWidth = 9
             i = .Cells(Rows.Count, "D").End(xlUp).Row
             icol = .UsedRange.Columns.Count
             For Each tCell In .Range("D1", "D" & i)
                With tCell
                    If Application.Proper(.Offset(, -1).Value) = "Department Total" Then
                        ic = 35
                    ElseIf tCell.Row = i Then
                        ic = 37
                    ElseIf Application.Proper(.Offset(, -2).Value) = "Total" Then
                        ic = 36
                    ElseIf Application.Proper(.Value) = "Total" Then
                        ic = 40
                    Else: ic = 0
                    End If
                End With
                With .Range(.Cells(tCell.Row, 1), .Cells(tCell.Row, icol))
                        .Interior.ColorIndex = ic
                        .Font.Bold = (tCell.Row = 1 Or ic > 0)
                End With
            Next
        End With
    Next
    With Application
    End With
End Sub
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
After a little work.... it is PERFECT, PERFECT, PERFECT!

Thank you!
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
alrighty, then. I am trying to adapt this code for my other four sheets that I am formatting in kind of the same way.

This part is working perfectly:
"Total" in Col C should format the row orange;
"Total" in Col B should format the row yellow;
"Department Total" in Col A should format the row green.

This works, except for the fact that I would like to BOLD all of the rows that contain "Total" in Col D. What do I add to this code to make it happen?

Code:
    Selection.AutoFilter Field:=4, Criteria1:="Total"
    With Application
        .ScreenUpdating = True
    End With
    Dim wsArray, ws As Long, tCell As Range, ic As Long, i As Long, icol As Long
        wsArray = Array("Sales By Product Pivot - trends")
    For ws = o To UBound(wsArray)
        With Sheets(wsArray(ws))
             .Columns("J:K").ColumnWidth = 9
             .Columns("E:F").ColumnWidth = 10
             i = .Cells(Rows.Count, "C").End(xlUp).Row
             icol = .UsedRange.Columns.Count
             For Each tCell In .Range("C1", "C" & i)
                With tCell
                    If Application.Proper(.Offset(, -2).Value) = "Department Total" Then
                        ic = 35
                    ElseIf tCell.Row = i Then
                        ic = 37
                    ElseIf Application.Proper(.Offset(, -1).Value) = "Total" Then
                        ic = 36
                    ElseIf Application.Proper(.Value) = "Total" Then
                        ic = 40
                    Else: ic = 0
                    End If
                End With
                With .Range(.Cells(tCell.Row, 1), .Cells(tCell.Row, icol))
                        .Interior.ColorIndex = ic
                        .Font.Bold = (tCell.Row = 1 Or ic > 0)
                End With
            Next
        End With
    Next
    With Application
    End With
 

Forum statistics

Threads
1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top