Test for coloured cells?

B

Becks

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
 
B

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)
 
G

Gary''s Student

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
 
B

Bob Phillips

F9 is simpler <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
E

exceluserforeman

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.
 
Top