Excel should be able to give back the colorcode of a cell via celi

[Bart Schouw]

It would be great if you could get returned the colorcode of certain cell,
based on this new calculcations are possible. For example some of my turnover
is based on pre-sales, these cells have have a certain color. It would be
great if i could count the number of cells with this color.

 

[JulieD]

Hi Bart

check out
http://www.cpearson.com/excel/colors.htm
for things you can do with cell colours

Cheers
julieD

 

[Bart Schouw]

Julie, this is exactly what I need, great stuff, thanks for the hint!
Cheers
Bart

 

[Arvi Laanemets]

Hi

Use an UDF. Like this one

Public Function GetColor(MyCell As Range) As Variant
GetColor = MyCell.Interior.ColorIndex
End Function


Now, the formula
=GetColor(A1)
returns the color code for cell A1. But be aware that changing cell color
doesn't trigger recalculating (and making the function volatile doesn't help
here) - you have to do it manually (pressing F9) or to wait until
recalculation is triggered by some change in cell values.

 

[JulieD]

Hi Bart

you're welcome ... Chip has lots of great stuff on his website - my other
favourite is Debra Dalgleish's at www.contextures.com/tiptech.html

more great sites are listed at www.mvps.org - under "Excel" on the right
hand side of the screen (unless they've redesigned their site again!) and a
bit of a google search will turn up a number of lists of good excel sites.

Cheers
JulieD

 

[Ola]

Hi,

Good simple solution Arvi.
=IF(NOW()>0,GetColor(A1),"") will also recalculate.

Ola

 

[Arvi Laanemets]

Hi

Hi,

Good simple solution Arvi.
=IF(NOW()>0,GetColor(A1),"") will also recalculate.

Did you test it?
Try it.
The function NOW() as any other function is recalculated only, when some
entry is changed. Cell color doesn't count as entry.

 

[Ola]

Hi Arvi,

Your formula =getcolor(A10) will not recalculate by F9.
It will only recalculate if the actual cell change (F2+Enter, copy/paste).
On the other hand =IF(NOW()>0,getcolor(A10),"") will recalculate by F9.
If I knew how, I would include cellcolor in morefunc.xll.

Ola

 

[JE McGimpsey]

If you're going to make the overall function volatile anyway, wouldn't
it be more efficient to use

=GetColor(A10)

and put

Application.Volatile

in GetColor(), and avoid the extra function calls?

 

[hrlngrv]

JE McGimpsey wrote...

If you're going to make the overall function volatile anyway, wouldn't

it be more efficient to use

=GetColor(A10)

and put

Application.Volatile

in GetColor(), and avoid the extra function calls?

This is a trade-off. If one puts Application.Volatile in the function,
then it's always volatile. If one only needs a few calls to be
effectively volatile, then that may be accomplished using Ola's trick,
though I prefer something like =f(x)+0*NOW() or =f(x)&LEFT(NOW(),0)
depending on what f() returns.

 

[JE McGimpsey]

True. In this case, I can't really imagine a practical scenario in which
one would want to mix volatile and static calls to the UDF...

 

[Arvi Laanemets]

Hi

You are right. I created it as volatile, tested it, and after that I decided
that the Volatile part is redundant and removed it - and forgot it. So the
function I tested was:

Public Function GetColor(MyCell As Range) As Variant
Application.Volatile
GetColor = MyCell.Interior.ColorIndex
End Function


Arvi Laanemets

 

[Wazooli]

I have inserted this code in my book.xlt, so it is always present. I know
nothing about VBA, so all this talk of volatility, etc... is completely not
understood by me. Is this code, as written, acceptable to be present in
large spreadhsheets? Will its presence have deleterious effects on
performance?

wazooli

 

[Arvi Laanemets]

Hi

I have inserted this code in my book.xlt, so it is always present. I know
nothing about VBA, so all this talk of volatility, etc... is completely not
understood by me. Is this code, as written, acceptable to be present in
large spreadhsheets? Will its presence have deleterious effects on
performance?

When the UDF is volatile (contains code 'Application.Volatile' at start),
it's recalculated whenever any cell entry is changed, regardless there is a
need for it or not.
When the UDF isn't volatile, it's recalculated only, when it's argument(s)
do change. So my function posted here at start recalculates only, when the
value in cell (A1 in my example) the function refers to is changed. Ola's
improvement forces recalculation for particular cell, whenever any cell is
changed (because time always changes) - i.e. the function behaves as
volatile for this particular cell.

You can use this function, as any other UDF, in any workbook. It doesn't
matter, is the workbook large or not, but it matters in how much cells you
use it. This function MUST be always volatile (through code, or through
Ola's trick), and when you use it in too many cells, then the workbook will
be slowed down considerably. I don't see any other negative effects though.


Arvi Laanemets