VBA Excel: Sheet Background corrector

Question

I created a form in VBA that sends data to a table. The whole Excel sheet where the table is has a orange background, but the table, that has no background, since I want the table to have its default format, which already has a background, as you can see here:

. If I added a new row with info to my table, it'd grow a row, but if I wanted to erase that row and resize the table back, that row would have no background now. Neither the table default background, nor the sheet orange background I want it to have.

I want to write code that:

if any cell in a range that is all cells in the sheet but the ones in the table has a different background interior.colorindex than 45, then make all the cells of the sheet have that background
and, make the cells of the table have no background interior.colorindex = 0.

.

Dim irowoffset As Long: irowoffset = Hoja2.Range("table2").Rows.Count
Dim belowtable2 As Range
belowtable2 = Hoja2.Range("table2").Offset("irowoffset")
Dim cell As Range
For Each cell In belowtable2
    If cell.Interior.ColorIndex <> 45 Then
        Hoja2.Cells.Interior.ColorIndex = 45
        Hoja2.Range("table2").Interior.ColorIndex = 0
    End If
Next cell

You could just change all cells on the sheet and then re-format the table rather looping. My question would be, why is this color scheme necessary? It is only acting to slow down your file in exchange for questionable aesthetic — urdearboy

Shelty Shelty · Accepted Answer · 2018-09-23T22:33:30

Well, put this code into the Worksheet module. It works quite well even though running through all the cells doesn't seem efficient.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim irowoffset As Long
    Dim belowtable2 As Range
    Dim cell As Range

    With Worksheets(1)

        irowoffset = .Range("table2").Rows.Count

        Set belowtable2 = .Range("table2").Offset(irowoffset)

        For Each cell In belowtable2
            If cell.Interior.ColorIndex <> 45 Then
                .Cells.Interior.ColorIndex = 45
                .Range("table2").Interior.ColorIndex = 0
            End If
        Next cell

    End With

End Sub

You may need to change the Worksheet to something else (I chose the first worksheet in the workbook, you may specify it preferably by name).

I assumed that Hoja2 is a worksheet.

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.change

https://www.contextures.com/xlvba01.html

Edit:

This code seems better:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim belowTable As Range
    Dim belowTableAll As Range

    With Worksheets(1)

        Set belowTable = .Range("table2").Resize(1, .Range("table2").Columns.Count).Offset(.Range("table2").Rows.Count, 0)
        Set belowTableAll = .Range(belowTable, .Cells(.Rows.Count, 1))

        belowTableAll.Interior.ColorIndex = 45
        .Range("table2").Interior.ColorIndex = 0

    End With

End Sub

VBA Excel: Sheet Background corrector

1 Answers