VBA Variable Row Formatting

zakasnak

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. 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:

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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
jasonb75 said:
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?
Click to expand...

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>
 
Upvote 0
jasonb75 said:
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?
Click to expand...

Help?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
After a little work.... it is PERFECT, PERFECT, PERFECT!

Thank you!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Macro to affect cell background color
Replies
3
Views
416
mumps
mumps
M
  • Solved
VBA to clear cell contents and conditional formatting but retain the formula
Replies
5
Views
472
MLC1980
M
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
439
rahildhody
R
B
  • Locked
  • Question Question
Multiply VBA Macro for hiding and unhiding rows
Replies
4
Views
562
RoryA
RoryA
N
  • Question Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
477
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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