Forum Discussion
Sandeeep
Aug 14, 2022Brass Contributor
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
- NikolinoDEGold Contributor
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 🙂
I know I don't know anything (Socrates)
- SandeeepBrass Contributor
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-15kI 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
- SandeeepBrass 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