monitoring a changing range of cells
 

Hi,
I am trying to find/create a function which allows me to monitor a changing
range of cells. I have a column in which the user types yes or no, and then
in another cell I am checking to see if all of the cells say "yes". My
problem is that I want the user to be able to add more rows of yes or no to
the column and to be able to check these new cells for the "yes or no"
condition. I am currently using function code that looks like this.

=IF(((AND(EXACT("YES",UPPER(range)))))=TRUE,"YES", "NO")

As you can see I have to give the function a defined range. I was thinking
of using a cell background colour checking function (the cells are
conditionally formatted to change colour when yes or no are typed in) that I
found on this page

http://cpearson.com/excel/colors.htm

to determine the range of cells to be checked, but I cannot get the
"RangeofColor" function to work, all I get is #value.

If anyone can help me out that would be greatly appriciated, and if anyone
has a simpler method of doing this I would love to hear it.

Conditional formatting can't be accessed from a UDF (at least not
easily). However, since your colors are determined by a formula, you can
use that formula to count.

If you want to determine whether there are all YESes or no NO's:

=IF(COUNTIF(A:A,"No"),"Not all YESes","No NOs")

Or

=IF(COUNTIF(A:A,"Yes")=COUNTA(A:A),"All YESes","Not all YESes")


If you can't use the entire column (or a known subset, like A2:A1000),
check out using a dynamic range:

http://cpearson.com/excel/named.htm#Dynamic

In article ,
"Kevin" wrote:

the cells are
conditionally formatted to change colour when yes or no are typed in) that I
found on this page

http://cpearson.com/excel/colors.htm

to determine the range of cells to be checked, but I cannot get the
"RangeofColor" function to work, all I get is #value.

If anyone can help me out that would be greatly appriciated, and if anyone
has a simpler method of doing this I would love to hear it.