Hi,

I have a small excel function that returns the background color value of a
cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

functions like index and offset will return a reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Hi,

I have a small excel function that returns the background color value
of a cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

Hi,

Thanks for the reply :)

Found an other way that should work, but...

ADDRESS(L4;$K$4;1;TRUE;"Personal") returns Personal!B21
ColorOfCell(Personal!B21) returns 3

BUT

colorofcell(ADDRESS(L4;$K$4;1;TRUE;"Personal"))
Gives "VALUE!" error message :(

Why not working?

Marton


"keepITcool" wrote in message
ft.com...


functions like index and offset will return a reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Hi,

I have a small excel function that returns the background color value
of a cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

Marton,

You function is being passed a range as its argument. HLOOKUP returns the
cell value, not the cell, so they have a basic dilemma.

You need to use a different function. MATCH will get you the column that A1
is in,

MATCH($A$1,$B$20:$B$51,0)

and INDEX can return the reference at the intersection of a column and a row

INDEX($B$20:$Q$51,J6, the_col)

Join them together and integrate with your function, and you get

=colorofcell(INDEX($B$20:$Q$51,J6,MATCH($A$1,$B$20 :$B$51,0)))

But also note that there is a basic problem with your function, in that it
will not update automatically if the cell colour is changed, you will need
to force a recalculation of the value with F9.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"VilMarci" wrote in message
...
Hi,

I have a small excel function that returns the background color value of a
cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

"functions like index and offset will return a reference,
but the lookup functions return a value."

Usage of the above has always confused me.
Clearly tell me the difference in "reference" and "value".
All I can presently say is that reference might be "B4",
whereby the value in B4 might be "100" or "testing".
I'm a bit more confused with the reference-side.
TIA,



"keepITcool" wrote in message
ft.com...


functions like index and offset will return a reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Hi,

I have a small excel function that returns the background color value
of a cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

Thank you very much. Now it's working.
Now it does all I really wanted.
Yes I know the bug that it won't update if color changes...
Is it possible (and how complicate?) to modify the code to update like
built-in functions?
Thanks,
Marton

"keepITcool" wrote in message
ft.com...


almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

in VBA

Dim rng as Range
Dim v as Variant
set rng = Range("B9")
v = Range("B9").Value

rng is a reference to the cell B9
v is a variable containing the value stored in B9.

If a function returns a reference to the cell, then it is designed to be
utilized by another function which requires a reference. if used alone,
then it is similar to using the default value of a range (which is its
value).

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:sx6Jd.22946$EG1.22013@lakeread04...
"functions like index and offset will return a reference,
but the lookup functions return a value."

Usage of the above has always confused me.
Clearly tell me the difference in "reference" and "value".
All I can presently say is that reference might be "B4",
whereby the value in B4 might be "100" or "testing".
I'm a bit more confused with the reference-side.
TIA,



"keepITcool" wrote in message
ft.com...


functions like index and offset will return a reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Hi,

I have a small excel function that returns the background color value
of a cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6; FALSE))

Is there any general solution to do this?

Marton

The fact that your function doesn't recalculate when the color changes
is not a bug. It's a design decision by the XL team, which decided
many versions ago that many changes, typically formatting related,
could not possibly have an impact on the contents of a worksheet.
Unfortunately, people have found ways of making that happen. ;-)

Also note that your function will fail if passed a range with more than
1 cell.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thank you very much. Now it's working.
Now it does all I really wanted.
Yes I know the bug that it won't update if color changes...
Is it possible (and how complicate?) to modify the code to update like
built-in functions?
Thanks,
Marton

"keepITcool" wrote in message
ft.com...


almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

As Tushar says, it is not a bug but a 'feature'.

The only way I have gotten around it is to have a button that changes the
cell colour, which includes a forced re-calculate.

Note also that it doesn't work on conditional formatted coloured cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VilMarci" wrote in message
...
Thank you very much. Now it's working.
Now it does all I really wanted.
Yes I know the bug that it won't update if color changes...
Is it possible (and how complicate?) to modify the code to update like
built-in functions?
Thanks,
Marton

"keepITcool" wrote in message
ft.com...


almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Nice feature :)

Anyway that's ok, I use a list, so if user change the name, it also reruns
the whole stuff...

Thanks everyone!

"Bob Phillips" wrote in message
...
As Tushar says, it is not a bug but a 'feature'.

The only way I have gotten around it is to have a button that changes the
cell colour, which includes a forced re-calculate.

Note also that it doesn't work on conditional formatted coloured cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VilMarci" wrote in message
...
Thank you very much. Now it's working.
Now it does all I really wanted.
Yes I know the bug that it won't update if color changes...
Is it possible (and how complicate?) to modify the code to update like
built-in functions?
Thanks,
Marton

"keepITcool" wrote in message
ft.com...


almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

In cell A2 - I have a number 123.45

Sub foo()
Dim rng As Range
Dim v As Variant
Set rng = Range("A2")
v = Range("A2").Value
End Sub

If I F8 down thru to End Sub without passing End Sub

In the Immediate Window I get:
? rng
123.45
? v
123.45

Can you provide a more diverse example of the useage
of Ref Vs Value?

Not sure I'm getting what you are saying...

-----Original Message-----
in VBA

Dim rng as Range
Dim v as Variant
set rng = Range("B9")
v = Range("B9").Value

rng is a reference to the cell B9
v is a variable containing the value stored in B9.

If a function returns a reference to the cell, then it is
designed to be
utilized by another function which requires a reference.
if used alone,
then it is similar to using the default value of a range
(which is its
value).

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:sx6Jd.22946$EG1.22013@lakeread04...
"functions like index and offset will return a
reference,
but the lookup functions return a value."

Usage of the above has always confused me.
Clearly tell me the difference in "reference"
and "value".
All I can presently say is that reference might be "B4",
whereby the value in B4 might be "100" or "testing".
I'm a bit more confused with the reference-side.
TIA,



"keepITcool" wrote in message

ft.com...


functions like index and offset will return a
reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

Hi,

I have a small excel function that returns the
background color value
of a cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in
functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!
$B$20:$Q$51;J6;FALSE))

Is there any general solution to do this?

Marton




.

What would you expect to see for the reference rng?

When you do
? rng
it is the same as
? rng.Value

both rng and v are variables. Each points to a location in memory. v
points to a location that stores the number 123.45 rng points to a
location in memory that has a pointer to the memory structure for
Range("A2") of the activesheet. If you refer to the rng variable directly
, it can't show you that memory structure, so it returns what is stored in
the default property which is the value property so you see 123.45.

--
Regards,
Tom Ogilvy

if you type ?rng in the immediate window
he'll give you the Range objects's default property
which is.. you guessed it.. .Value

make sure the the "LOCALS" window is visible.
now use F8 to step thru again, and keep a close eye on that window.

but in the locals window you can see (and learn)
about all the properties of your object variables.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

If I F8 down thru to End Sub without passing End Sub

In the Immediate Window I get:
? rng
123.45
? v
123.45

Can you provide a more diverse example of the useage
of Ref Vs Value?

Not sure I'm getting what you are saying...

Hi,

Hm.. something still not good... however it's not on the function side...

I did some select case mods at the end to display text rather than color
code.
The function is ok now, but:

1. ColorOfCell(I7) returns "PH"
2. ColorOfCell(I7)="PH" returns TRUE

3. ColorOfCell(INDIRECT(ADDRESS($P8;$O$4;4;TRUE;"Pers onal"))) returns "PH"
4. ColorOfCell(INDIRECT(ADDRESS($P8;$O$4;4;TRUE;"Pers onal")))="PH" returns
"volatile"

I don't understand why line 4 is not working. I'd like to work with the
returned value further...
One more thing:
Conditional formatting based on the "PH" string entered into the cell by the
finction is working...

What's the difference between line 2 and 4?

Thanks for any help,

Marton


"keepITcool" wrote in message
ft.com...


almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :