I am big fan of arrays. Since I found out about arrays I have been trying to use them everywhere! Or maybe just there where it is possible.
I recently wrote about them, if You missed it I highly recommend reading my post https://simpleexcelvba.com/arrays-one-of-the-best-things-you-can-learn-in-excel-vba/ We all know that arrays can store values, for example 2 dimensional array can hold a Excel table values. But can arrays store cell color property?
Everyone will tell You, that it is not possible. That arrays store only cell values, not their properties and that’s why operating on them is so much faster – array “cells” are lighter than standard table cells.
I agree with those people… and also kinda disagree. Why?
You are not limited by 2 dimensions, so why not use next one?
Let’s use array 3rd dimension! To declare value and chosen cell property. In most of cases we need a cell fill or font color, so only one additional place in array.
Take a look at the code I prepared.
Checking how many rows and columns are filled and declaring its first place in 3rd dimension for cell value.
zakres(i, j1, 1) = .Cells(i, j1)
And for cell interior color.
zakres(i, j1, 2) = .Cells(i, j1).Interior.ColorIndex
Here is the first part:
Sub main1() Dim lastRow As Long Dim lastCol As Long Dim ws As Worksheet Dim comper As Range Dim j1 As Long Dim i As Long Dim zakres As Variant 'our 3 dimension Array Set ws = Sheet1 'set your worksheet With ws lastRow = .Cells(Rows.Count, 1).End(xlUp).Row lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column ReDim zakres(1 To lastRow, 1 To lastCol, 1 To 2) For i = 1 To lastRow For j1 = 1 To lastCol zakres(i, j1, 1) = .Cells(i, j1) 'cells value zakres(i, j1, 2) = .Cells(i, j1).Interior.ColorIndex 'cells color Next Next
After all things we want to do on that table – I mean on array – just paste all the values to your worksheet to cells value and to cell color property.
For i = 1 To lastRow For j1 = 1 To lastCol .Cells(i, j1) = zakres(i, j1, 1) .Cells(i, j1).Interior.ColorIndex = zakres(i, j1, 2) Next Next End With End Sub
So! This is it! The alternative for standard approach! Maybe not directly, but arrays can actually store cell color property, in their own way!
You may said, that it will be slower than standard table. That all additional dimensions, values, colors etc.
I checked that. Go to: https://simpleexcelvba.com/array-vs-table-fill-color-property-speed-comparison/