Identify Cell Color Index

V

Voskre

Hi,
I'm using Excell 2000.

In cell B1 I want to insert the color index of cell A1 and I'm using the
following function copied from Internet:

Function showColorIndex(targetcell)
showColorIndex = targetcell.Interior.ColorIndex
End Function

In B1 I get the number -4142 and it can not be.
Even for the next rows (i.e. B2 for A2 color, B3 for A3 color etc., all with
differente colors) I always get -4142.

What's wrong with that function as all AddIns are duly installed?

Thanks for any help.

Dominic.
 
G

Gord Dibben

Voskre

In B1 enter =showColorIndex(A1) and drag/copy down column B.

Works for me if column B is formatted to general and Auto-calcuation is set.

-4142 is the default cell color "no fill"

Gord Dibben Excel MVP
 
J

JE McGimpsey

Are your colors set by Conditional Formatting? showColorIndex won't pick
that up - you'll need a function that checked the same conditions as
your CF.

-4142 is the colorindex of a cell with no color applied.
 
V

Voskre

Hi Gord,

before writing to the NG, I did as you suggested: now I did it once again
but it does not work.
sorry....
I can send my file if you like.

Thanks in any case.

Dominic
 
B

Bob Phillips

Dominic,

assuming that you are not using conditional formatting colouring, take a
look at this page

http://www.xldynamic.com/source/xld.ColourCounter.html
Processing Coloured Cells

It's quite a bit more complicated thatn your simple function, but it caters
foir the defaults.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
V

Voskre

Thank for any help.
I did not use the conditional formatting.

As per Bob Phillips suggestion I used the:

"
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant

"
from the page you mentioned but I got for any cell I referred to always the
same number i.e. 2

Thanks again for yr attention.

Dominic
 
B

Bob Phillips

You said you did not use conditional formatting!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top