troubleshooting Question

Excel VBA: apply specific format

Avatar of Luis Diaz
Luis Diaz asked on
VBAMicrosoft ExcelMicrosoft Office
12 Comments2 Solutions92 ViewsLast Modified:
Hello experts,
I have the following code in order to apply format for for specific range.
I would like to create an Apply_Format procedure to add to my personal.xlsb with the following requirement:
1.      Inputbox: “Select initial range (header range) in which you want to apply format”. Error handling: exit sub if the data reported is not correct or exit sub if cancel button is activated.
Here is what I have for the moment:

Sub Apply_Format()
Dim wb As Workbook
Dim ws As Worksheet
Dim iX As Integer

Set ws = ActiveSheet
With ws
'Remove gridlines
ActiveWindow.DisplayGridlines = False
'Apply Borders
With .Borders
.LineStyle = xlContinuous
.ThemeColor = 7
.TintAndShade = 0
.Weight = xlThin
End With
'Apply Font
With .Font
.Name = "Segoe UI"
.Size = 11
End With
'Set columns width
For iX = 1 To tbl.ListColumns.Count
tbl.ListColumns(iX).Range.ColumnWidth = 20
Next iX

'Apply Alignement
With .HorizontalAlignment = xlLeft
End With
End With
'Apply fill color for header row
'With .HeaderRowRange
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 26112
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Apply font white
With .Font
.Color = vbWhite
.Name = "Segoe UI"
End With
End With
End Sub

Open in new window

Robberbaron (robr)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros