Test for coloured cells?
 

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

Test for coloured cells?
 

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)

"Becks" wrote in message
...
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

Test for coloured cells?
 

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
--
Gary''s Student


"Becks" wrote:

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

Test for coloured cells?
 

F9 is simpler <vbg

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gary''s Student" wrote in message
...
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
--
Gary''s Student


"Becks" wrote:

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

Test for coloured cells?
 

Thanks very much both of you!

Becks

"Bob Phillips" wrote:

F9 is simpler <vbg

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gary''s Student" wrote in message
...
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
--
Gary''s Student


"Becks" wrote:

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

Test for coloured cells?
 

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.



"Becks" wrote:

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