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

[Sandeeep]

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)

ID	Status	EventHappen	Reason	Gone?	Total  Time	Name
1001	CLOSED	TRUE	he ran	FALSE	0.000416667	Jack
1002	CLOSED	FALSE		FALSE	0.000219907	Davis
1003	CLOSED	FALSE		FALSE	0.001863426	Drew
1004	CLOSED	FALSE		TRUE	0.000208333	Kumar
1005	CLOSED	FALSE		FALSE	0.000150463	Kiran
1006	CLOSED	FALSE		FALSE	0.027916667	Allysa
1007	CLOSED	FALSE		FALSE	0.003148148	Tom
1008	CLOSED	FALSE		FALSE	0.006643519	Brady
1009	CLOSED	FALSE		FALSE	3.47222E-05	Brandon
1010	CLOSED	TRUE	she jumped	FALSE	0.001134259	Joe
1011	CLOSED	FALSE		FALSE	0.000613426	Jack
1012	CLOSED	FALSE		FALSE	0.006851852	Jacky
1013	CLOSED	FALSE		FALSE	0.004363426	William
1014	CLOSED	FALSE		FALSE	0.003969907	Selena
1015	CLOSED	TRUE	they swam	FALSE	0.002268519	Joesph
1016	CLOSED	TRUE	I don’t know	FALSE	0.000914352	Joesph

Please do assist. Thank you!

[#2]

@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)

[#3]

@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

[#4 · Accepted solution]

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