How do I prevent an excel table's formatting from overwriting a range's existing formatting in VBA?

Question

I'm working with a spreadsheet that requires the use of a table. I resize it to only include the headers and the first line of data I'm working with, but when I then refit it to cover all of my existing data, it overwrites the formatting and resets the color back to default white. I need these colors as they are referenced later in the code. Is there a way to prevent the table from doing this?

Dim FLF As Worksheet
Set FLF = Workbook("My Workbook").Sheets("FLF")

Dim x As Long
Dim lng As Long

With FLF
FLF.Activate

.ListObjects("Table1").Resize Range("$A$6:$K$7")

lng = .Cells(.Rows.count, "D").End(xlUp).Row

.Range("E7:G" & lng).NumberFormat = "0.00%"

.ListObjects("Table1").Resize Range("$A$6:$K$" & lng)

For x = 7 To lng
    If .Range("A" & x).Interior.ColorIndex = 46 Then
        TopPercent = .Range("K" & x).Value
        Do
            x = x + 1
            .Range("K" & x) = TopPercent * .Range("F" & x).Value
            .Range("K" & x).Font.FontStyle = "Italic"
        Loop While .Range("A" & x + 1).Interior.ColorIndex = 36
    End If
Next x

Misc notes - you have `with FLF`, so there's [no need to use `FLF.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also, you have an unqualified `Range()` in the `.ListObjects()` lines. Also, I would also push the code within the `With` block over an indent (like you do with `For...`). — BruceWayne, Jan 24 '20 at 15:28

Czeskleba · Accepted Answer · 2020-01-25T13:19:03.957

I tried to recreate the thing you are describing. I think I'm missunderstanding whats happening.

I made a table, put some data below the table, colored the cells yellow and then dragged that little blue thing in the bottom right down over my yellow data to include it in the table. For me it keeps the formatting.

The only thing I can think of, is that you manually applied some form of filling to the table? You could change the table style and formatting. If you want to keep your table, the only thing I can think of is to save the colorindex of the important range before expanding the table and then reformatting that range after you have expanded it. This could be done in several ways but since we are already in VBA, how about this?

'I don't know how to Dim this in one line, sorry
Dim ColorIndexArray()
ReDim ColorIndexArray(ThisWorkbook.Sheets("FLF").Cells(ThisWorkbook.Sheets("FLF").Rows.Count, "A").End(xlUp).Row)

For i = 1 To ThisWorkbook.Sheets("FLF").Cells(ThisWorkbook.Sheets("FLF").Rows.Count, "A").End(xlUp).Row
    ColorIndexArray(i) = ThisWorkbook.Sheets("FLF").Range("A" & i).Interior.ColorIndex
Next

'Do your stuff

For i = 1 To ThisWorkbook.Sheets("FLF").Cells(ThisWorkbook.Sheets("FLF").Rows.Count, "A").End(xlUp).Row
    ThisWorkbook.Sheets("FLF").Range("A" & i).Interior.ColorIndex = ColorIndexArray(i)
Next

Edit: I tried loading all indices into the array at once and failed, hence the loop. Also, if you have tens or hundreds of thousands of rows, and you already have an array now, you could do your calculations on it instead to speed things up? But if its only a couple lines it shouldnt matter.

Sorry, should've been more specific. The preceding code to what I've posted colors the lines of data with one of 3 different color indices. In this case, the first line of data is orange, followed by yellow and blue lines. On the first "Resize" command to just resize the table to contain the headers and first line of code, it appears to reformat the table to make all the data orange by default. I say this because when I then resize it again to cover all of my data, every one of the lines becomes orange, so I lose my ability to reference these lines through their differing color indices. — Jared Rondeau, Jan 27 '20 at 14:24
Ahh I see, I had that problem a lot as well, it has something to do with how you format the table. Select somewhere in the table, and then change the tableformatting. (not manually apply colors and stuff but change the template). Also when excel expands a table, it sometimes takes the formatting of the first row and continues it down when adding a row. Without having the table in front of me its difficult to tell what exactly is going on. Try a Range.ClearFormats before executing the code, or add an empty row as a buffer. (I used both methods in the past) — Czeskleba, Jan 27 '20 at 14:34
Also, this worked! Thanks! One strange caveat before moving on: When I use "A6:K" & lng instead of "$A$6:$K$" & lng, it leaves the topmost data line orange and colors then others white instead of coloring them all the same orange as the top line. But in any event, using an holder array like you did did the trick. Thanks! — Jared Rondeau, Jan 27 '20 at 14:34

How do I prevent an excel table's formatting from overwriting a range's existing formatting in VBA?

1 Answers1