SOLVED

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

Occasional Contributor

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, 

Sandeeep_1-1660514748279.png

 

What I need to do is,

Sandeeep_2-1660514776037.png

 

So I can make it look like this

Sandeeep_3-1660514826382.png

 

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!

 

3 Replies

@Sandeeep 

Does it have to be with VBA?

This is a bit more cumbersome and slows down the workbook.

Why not use conditional formatting? or with filter by color?

 

Here is a small correction in your code (inserted file).

We don't know if this helps, but... who knows

 

NikolinoDE

I know I don't know anything (Socrates)

@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
best response confirmed by Sandeeep (Occasional Contributor)
Solution

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

Sandeeep_0-1660584419830.png

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