COLOR INDEX UPDATING & FOR CONDITIONAL FORMATTING!

[FARAZ QURESHI]

Hi friends,

One of the reputable experts advised me to use the following code to return
the color index of a cell:

Public Function Color(ByVal rgeCell As Range) As Integer
Color = rgeCell.Interior.ColorIndex
End Function

The problem is that reply of such a function upon "change" of the colour of
the target cell doesn't update and I have to manually use F2 and then ENTER
again so as to update the value or copy and paste the same formula.

Furthermore any professional advice to have have such a formula working for
CONDTIONAL FORMATTING shall highly obliged.

Thanx in advance to all you pals.

FARAZ

 

[Dave Peterson]

First, this function won't update when formatting is changed. Formatting
doesn't cause excel to recalc.

You could make the function volatile:
Public Function Color(ByVal rgeCell As Range) As Integer
application.volatile
Color = rgeCell.Interior.ColorIndex
End Function

But this still could be one calculation behind--leading to incorrect results.

Second, this function returns the colorindex of the cell when you apply the
formatting--not the color from conditional formatting.

If you want to try to return the color based on conditional formatting, you
could review the code from Chip Pearson's site:

http://cpearson.com/excel/CFColors.htm

I think it's far from trivial. You may want to use another cell that mimics the
same conditions, but returns a number. It may be lots easier.

 

[Joel]

Excel doesn't recognize a change of color as an Event. Events are what
causes the spreadsheet to get updated and causes macros to run. Therefore,
colors cannot cause any updates of calculations nor can it cause a macro to
run .