ColorIndex

[robbie88]

Hello,

please can you help me with this small problem?

I have this function:

Please Login or Register  to view this content.

But when I try to get the value of e.g. Yellow color in A1 position, I will ge just error note: VALUE. I thiet different types of formating, but nothing help.

Thanks
Robbie88

[Kaper]

Try:

Please Login or Register  to view this content.

I tried to highlight ir with red, but it is hardly visible. there is underscore between Cell and Color

[robbie88]

Hello Kaper,

it works perfectly now!!! Thank you.

[Kaper]

PS. see attached file and note that the value does not change when you change color.
To force change after recalculation (not after color change) one can for instnce use Volatile or such trick as I used in attachment

[robbie88]

Hello Kaper,

thank you fir your replay. I checked your code - but it doesn't work correctly in my Excel. When I change the color I have to "recalculate"/"refresh" the cell with code. But it will be nice to do it automatically.

Thanks

[Kaper]

Changing format does not fire an event in Excel.
a mere substitute could be in sheet code:

Please Login or Register  to view this content.

it works just after you change focus (select another cell) (not directly after formatting change) and is reasonable to use only if there is not too many formulas in the sheet

[robbie88]

Thank you.