I currently have this code, which applies to a border to all cells with information in all worksheets in a given workbook. I would now like to add to this code so that it:
- Changes all cells to one color
- Changes the left most two columns to another color
- Changes the top two rows to the same color and change the font to the same color
(I have provided what my code currently outputs and what I want the goal output to be)
I have multiple worksheets I need to do this for but the last column/ last row with information varies from worksheet to worksheet
Current Output:
Goal Output:
- Changes all cells to one color
- Changes the left most two columns to another color
- Changes the top two rows to the same color and change the font to the same color
(I have provided what my code currently outputs and what I want the goal output to be)
I have multiple worksheets I need to do this for but the last column/ last row with information varies from worksheet to worksheet
VBA Code:
Sub AllWorksheetBorders()
Application.ScreenUpdating = False 'Prevents screen refreshing
Dim lngLstCol As Long, lngLstRow As Long, ws As Worksheet
Dim rngCell As Range, r As Long, c As Long
For Each ws In ActiveWorkbook.Worksheets
lngLstRow = ws.UsedRange.Rows.Count
lngLstCol = ws.UsedRange.Columns.Count
For Each rngCell In ws.Range("A1:A" & lngLstRow)
If rngCell.Value <> "" Then
r = rngCell.Row
c = rngCell.Column
With ws.Range(ws.Cells(r, c), ws.Cells(r, lngLstCol)).Borders
.LineStyle = xlContinuous 'Setting style of border line
.Weight = xlThin 'Setting weight of border line
.ColorIndex = xlAutomatic 'Setting colour of border line
End With
End If
Next
Current Output:
Book1345.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | First | Last | 1 | 2 | 3 | 4 | 5 | ||
2 | aa | bb | cc | dd | ee | ||||
3 | Ali | Brown | X | ||||||
4 | Bill | Patel | X | X | |||||
5 | Catherine | Gomez | X | X | |||||
6 | Delilah | Harrington | X | ||||||
7 | Eva | Lee | X | X | X | X | |||
8 | Freya | Miller | X | X | X | ||||
9 | Gina | Williams | X | X | X | ||||
10 | Herrod | Smith | X | X | X | ||||
11 | Isabelle | Cooper | X | ||||||
12 | John | Wakefield | X | X | X | ||||
Sheet6 |
Goal Output:
Book1345.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | First | Last | 1 | 2 | 3 | 4 | 5 | ||
2 | aa | bb | cc | dd | ee | ||||
3 | Ali | Brown | X | ||||||
4 | Bill | Patel | X | X | |||||
5 | Catherine | Gomez | X | X | |||||
6 | Delilah | Harrington | X | ||||||
7 | Eva | Lee | X | X | X | X | |||
8 | Freya | Miller | X | X | X | ||||
9 | Gina | Williams | X | X | X | ||||
10 | Herrod | Smith | X | X | X | ||||
11 | Isabelle | Cooper | X | ||||||
12 | John | Wakefield | X | X | X | ||||
Sheet5 |