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

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.

Hi Bart

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

Cheers
julieD

"Bart Schouw" <Bart [email protected]> wrote in message
news:[email protected]...
> 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.

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

"JulieD" wrote:

> Hi Bart
>
> check out
> http://www.cpearson.com/excel/colors.htm
> for things you can do with cell colours
>
> Cheers
> julieD
>
> "Bart Schouw" <Bart [email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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.


--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Bart Schouw" <Bart [email protected]> wrote in message
news:[email protected]...
> 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.

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

"Bart Schouw" <[email protected]> wrote in message
news:[email protected]...
> Julie, this is exactly what I need, great stuff, thanks for the hint!
> Cheers
> Bart
>
> "JulieD" wrote:
>
>> Hi Bart
>>
>> check out
>> http://www.cpearson.com/excel/colors.htm
>> for things you can do with cell colours
>>
>> Cheers
>> julieD
>>
>> "Bart Schouw" <Bart [email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

Hi,

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

Ola

Hi


"Ola" <[email protected]> wrote in message
news:[email protected]...
> 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.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets

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

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?

In article <[email protected]>,
Ola <[email protected]> wrote:

> 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.

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.

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...

In article <[email protected]>,
[email protected] wrote:

> 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.

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


"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
>
> "Ola" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>

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" wrote:

> 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.
>
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
> "Bart Schouw" <Bart [email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

Hi


"Wazooli" <[email protected]> wrote in message
news:[email protected]...
> 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