Test for coloured cells?

[Guest]

I have entered a UDF given to me in an earlier question and it works. But,
if cells are altered it does not update as normal formulas do? Here's what
i've entered:

Function IsRed(r As Range) As Integer
IsRed = 0
If r.Interior.ClorIndex = 3 Then
IsRed = 1
End If
End Function

Then in the cell i want the answer:

=IF(IsRed(A1)=1,"P","")

If a cell is red I want to record "P" If clear "", but i want it to change
from "" to "P" if the cell is coloured at a later date. Any Ideas?

Thanx
Becks

 

[Bob Phillips]

Unfortunately a colour change will not force a recalculation.

You could add Application.Volatile at the start and hit F9 when you change a
colour, but not much else.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob is correct.

If you make the cell red by using Format > Cells... Excel is not bright
enough to realize that the cell has changed in a way to require
re-calculation of the function.

If, however, you have a red cell elsewhere on the worksheet, copy it and
paste in to A1. Then Excel realizes that IsRed() should be re-calculated

 

[Bob Phillips]

F9 is simpler <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks very much both of you!

Becks

F9 is simpler <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Why not make your determinations on the value of the cell.
Rather than rely on color.

Otherwise you will have to use code to determin the "color" of the cell.