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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments