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
- Yes, I know there are comments on my code, that would be me trying various things and failing for the past 3hrs.
- 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.
- 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.
- 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!
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