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.



FARAZ QURESHI wrote:

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