Test for coloured cells?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
F9 is simpler <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
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.
 
Back
Top