CODEWITHSUNDEEP
vbaexcel
Dimitar Stratiev
Dimitar Stratiev

Reputation: 31

VBA - retrieving cell colours for all cells in a range all at once

I'm working on an optimization task for our data reconciliation workbook and part of the work the code does is to colour certain cells a specific color based on some conditions. I would like to retrieve all cell colours in a 2D array similarly to how you can retrieve all cell values in a range using:

Dim myRangeVals() as Variant
myRangeVals = someRange.value

I tried using the following code but Interior.Color returns a Double:

Dim myCellColours() as Variant
myCellColours = someRange.Interior.Color

Is there a way to retrieve all cell colours all at once(without looping of course)?

Thanks

Upvotes: 0

Views: 267

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

Is there a way to retrieve all cell colours all at once(without looping of course)?

No, it is not.

Have a look at Range Object (Excel).

There you will find Range.Value Property (Excel) which has a property RangeValueDataType which is xlRangeValueDefault by default. And this means:

...If the Range object contains more than one cell, returns an array of values...

Thats why the Range.Value may be returning an array.

But Range.Interior Property (Excel):

Returns an Interior object that represents the interior of the specified object.

This means one Interior Object (Excel) only for the whole given Range.

Upvotes: 2

Related Questions