How to store fill and font color of every cell in a range in Excel VBA

When I refresh the data from my database, any fill/font color gets moved when new rows are added. I want to add to my refresh macro

1. Store current fill/font color information
2. Clear it all
3. Refresh data
4. reapply colors to proper cells

This is my current code that does the same thing with my manual notes columns

    plannerData = plannerSheet.Range("A3:BO" & CStr(lastRowPlanner))
    historyData = historySheet.Range("A2:BO" & CStr(lastRowHistory))
    lastRowPlannerData = UBound(plannerData, 1)
    lastRowHistoryData = UBound(historyData, 1)


    For plannerRow = 1 To lastRowPlannerData
        plannerValue = plannerData(plannerRow, 4) 'get the work order number
        
        For historyRow = 1 To lastRowHistoryData
            historyValue = historyData(historyRow, 4) 'check the work order number in this row
            
            If plannerValue = historyValue Then 
                For i = LBound(columnsToCopy) To UBound(columnsToCopy)
                    plannerData(plannerRow, columnsToCopy(i)) = historyData(historyRow, columnsToCopy(i))
                Next i
                
                Exit For 'no need to keep searching the history, move on to the next planner row
                
            End If
            
        Next historyRow
    
    Next plannerRow
    
    'so now we have restored planner data in memory
    'we can reset the planner spreadsheet accordingly
    plannerSheet.Range("A3:BO" & CStr(lastRowPlanner)).Value = plannerData

I'm not sure if I can modify this code to work with the colors since currently it's just dealing with text

Dim i As Long    
Dim fillColor() As Variant
Dim fillColorDimension As Long

fillColorDimension = 0
For Each cell In plannerSheet.Range("A3:BT" & lastRowPlanner)
    If cell.Interior.Color <> 16777215 Then
     fillColorDimension = fillColorDimension + 1
    End If
Next cell

If fillColorDimension <> 0 Then
    i = 1
    ReDim fillColor(1 To fillColorDimension, 1 To 3)
    For Each cell In plannerSheet.Range("A3:BT" & lastRowPlanner)
        If cell.Interior.Color <> 16777215 Then
            cellRow = cell.Row
            fillColor(i, 1) = plannerSheet.Range("D" & cellRow).Value
            fillColor(i, 2) = cell.Column
            fillColor(i, 3) = cell.Interior.Color
            i = i + 1
        End If
    Next cell
End If

ThisWorkbook.RefreshAll

For colorRow = 1 To fillColorDimension
    For Each cell In plannerSheet.Range("D3:D" & lastRowPlanner)
        If cell = fillColor(colorRow, 1) Then
            plannerSheet.Cells(cell.Row, fillColor(colorRow, 2)).Interior.Color = fillColor(colorRow, 3)
        End If
    Next cell
Next colorRow

My workbook is 72 columns long and I tested it with ~700 rows. If all 50,400 cells have a fill color, this takes about 25 seconds. Considering my users will likely not be highlighting more than 10% of those cells, it should be much faster than that.

I use column D because that is where the work order number is stored. This acts as a primary key for the data. If a data set does not have a unique key like this, it would need an artificial one. This, i believe, is usually done with an index(?)

Also, this does not copy the fill colors to the history sheet. The history sheets main point was to track WOs that fell off after refresh because of closure, and tracking their notes on those was important. Colors, not so much. But the same process could be applied to copy colors from the planner sheet to the history sheet