Formatting Multiple Sheets in Terms of Cell and Font Color using VBA

[aryaden]

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

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

 

[Fluff]

How about

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
            With ws.UsedRange
               .Interior.Color = 14083324
               .Columns(1).Interior.Color = 8696052
               .Columns(2).Interior.Color = xlNone
               .Rows("1:2").Interior.Color = 1137094
               .Rows("1:2").Font.Color = vbWhite
            End With
         Next ws
End Sub

 

[aryaden]

How about

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
            With ws.UsedRange
               .Interior.Color = 14083324
               .Columns(1).Interior.Color = 8696052
               .Columns(2).Interior.Color = xlNone
               .Rows("1:2").Interior.Color = 1137094
               .Rows("1:2").Font.Color = vbWhite
            End With
         Next ws
End Sub

Thanks I'll try that!