Forum Discussion

Sandeeep's avatar
Sandeeep
Brass Contributor
Aug 14, 2022

VBA: Select multiple Columns till the last row to add a background color.

VBA code help

Select multiple Columns till the last row to add a background color with easy-to-read code.

Spent the last 3hrs trying everything I could find on the internet. 

 

Problem:

I got columns from A:AA, all filled with data (mostly).

Column A will always be filled with an ID, whereas other columns may not.

I'd like to make it all colorful, so it's easier to look at.

 

Here is an example, 

 

What I need to do is,

 

So I can make it look like this

 

Usecase

Select non-consecutive Columns (with no patterns, such as ABC skip EFG skip, etc)

Add a Background color

Finally, for very certain columns, cause there are BLANKS, highlight those that aren't with a different color, and make the text italic.

 

What is successfully done so far?

Able to get the first set of columns selected and colored via

Range(Selection, Selection.End(xlDown)).Select

 

But including more ranges like 

Range("A2:E2,F2,G2:I2,L2:M2,N2,T2:V2,W2:Z2,AA2").Select

Range(Selection, Selection.End(xlDown)).Select

Does not work for all, only for A2:E2

 

This was Successful

I'm also adding borders for each colored set of columns, and intended for those columns with TRUE/FALSE to be conditionally formatted, then with borders around each cell.

 

Range("A1").CurrentRegion.Select
    With Selection
'        On Error Resume Next
'        .SpecialCells(xlCellTypeBlanks).Value = "0"
        .BorderAround Weight:=xlMedium
    End With

 

 

 

Use-Case, my Problem: Kinda works (code below)

 

' Setting User ID Block = Pink Colors
    Range("A2:E2").Resize(Lastrow).Select
'    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 14931698
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
' Setting Certain column Colors
    Range("F2:F2,Y2:Z2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With

' Setting Secret Human column Stats Colors
    Range("G2:I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    With Selection
'        .Borders(xlDiagonalDown).LineStyle = xlNone
'        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders.Weight = xlThin
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    
' Setting TRUE & FALSE CONDITIONAL Colors
    Range("J2:K2,O2:S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="False", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="True", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    

 

 

Notes

  1. Yes, I know there are comments on my code, that would be me trying various things and failing for the past 3hrs.
  2. I tried For Each loop, the array needs to be defined previously. Given I have so many ranges, this will end up with 10-15 ranges that'll just be used once in the code and feel very redundant.
  3. I've also tried to get the last row, via a variable and count. Then attempt to truncate that to the selection range formulas with no success.
  4. Brute force manual code for each task does work but ends up being 500+ lines of code, and I'd love to reduce it all.

 

Example's Data (shown in the Pic above, so you can copy-paste it)

 

IDStatusEventHappenReasonGone?Total  TimeName
1001CLOSEDTRUEhe ranFALSE0.000416667Jack
1002CLOSEDFALSE FALSE0.000219907Davis
1003CLOSEDFALSE FALSE0.001863426Drew
1004CLOSEDFALSE TRUE0.000208333Kumar
1005CLOSEDFALSE FALSE0.000150463Kiran
1006CLOSEDFALSE FALSE0.027916667Allysa
1007CLOSEDFALSE FALSE0.003148148Tom
1008CLOSEDFALSE FALSE0.006643519Brady
1009CLOSEDFALSE FALSE3.47222E-05Brandon
1010CLOSEDTRUEshe jumpedFALSE0.001134259Joe
1011CLOSEDFALSE FALSE0.000613426Jack
1012CLOSEDFALSE FALSE0.006851852Jacky
1013CLOSEDFALSE FALSE0.004363426William
1014CLOSEDFALSE FALSE0.003969907Selena
1015CLOSEDTRUEthey swamFALSE0.002268519Joesph
1016CLOSEDTRUEI don’t knowFALSE0.000914352Joesph

 

Please do assist. Thank you!

 

  • Found it. https://www.exceldome.com/solutions/color-non-blank-cells/

    Didn't have to dim declare TextCell

        For Each TextCell In Selection
            If TextCell <> "" Then
                TextCell.Font.Italic = True
                TextCell.Interior.Pattern = xlSolid
                TextCell.Interior.PatternColorIndex = xlAutomatic
                TextCell.Interior.ThemeColor = xlThemeColorAccent4
                TextCell.Interior.TintAndShade = 0.399975585192419
                TextCell.Interior.PatternTintAndShade = 0
            End If
        Next

     

    • Sandeeep's avatar
      Sandeeep
      Brass Contributor

      NikolinoDE 

      Yep, needs to be VBA.
      The point is to beautify my data.

       

      I get 5 excel files, every day in the same fashion.
      excepts values are different. Row count ranging from 15-15k

       

      I just want them to all look nicer, and have my eyes dart/go to where it's immediately needed.
      And I don't wanna keep doing conditional formatting 27 columns 5-7 times a day 25 days a month, with multiple clicks or spend lots of time.
      Just one quick macro and I'm done.

       

       

      Btw, can you help me with the condition formatting of column D, labeled 'REASON'.
      I need for the selection to identify if the cell is empty or not.
      If it is not empty, then change the formatting (BGcolor and font)

      I tried using a IF THEN, for each. with ISEMPTY and ISTEXT.
      it doesn't seem to do it.

       

      As for all the other questions, I guess if it's too complex, I'll copy-paste the same code for each colored blocks. (makes the code twice as long, but at least it works)

       

       

      Here is the code, I currently have, that applies a BG color to the selected range, but again doesn't apply a different formatting to Non-Empty cells in the same selected range.

       

      Range("L2:M2").Resize(Lastrow).Select ' Where Lastrow is the last populated row in ColA
          With Selection.Interior ' BG color all the selected cells
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorAccent4
              .TintAndShade = 0.599993896298105
              .PatternTintAndShade = 0
          End With
      
      ' The if statement to find which of the cells in the range is Non-Empty and add 
      ' different formatting, i.e making it gold and italic
          With Selection
              If IsEmpty(Selection.Value) = True Then
                  .Font.Italic = True
                  .Pattern = xlSolid
                  .PatternColorIndex = xlAutomatic
                  .ThemeColor = xlThemeColorAccent4
                  .TintAndShade = 0.399975585192419
                  .PatternTintAndShade = 0
              End If
          End With
      ' Adding Borders to this selection    
      With Selection ' Adding Borders
              .Borders.Weight = xlThin
              .Borders(xlInsideVertical).LineStyle = xlNone
              .Borders(xlInsideHorizontal).LineStyle = xlNone
          End With
      • Sandeeep's avatar
        Sandeeep
        Brass Contributor

        Found it. https://www.exceldome.com/solutions/color-non-blank-cells/

        Didn't have to dim declare TextCell

            For Each TextCell In Selection
                If TextCell <> "" Then
                    TextCell.Font.Italic = True
                    TextCell.Interior.Pattern = xlSolid
                    TextCell.Interior.PatternColorIndex = xlAutomatic
                    TextCell.Interior.ThemeColor = xlThemeColorAccent4
                    TextCell.Interior.TintAndShade = 0.399975585192419
                    TextCell.Interior.PatternTintAndShade = 0
                End If
            Next

         

Share

Resources