checking cell color

  • Thread starter Thread starter nimrod033
  • Start date Start date
N

nimrod033

I have a lotto pool spreadsheet with the winning numbers highlighted
yellow.

The entrants are in column a and their numbers are in column C through
L. I have one condition that picks the numbers out and colors them
yellow.

Is there a way to check the cell color from C to L of each entrant to
see if it is all colored yellow, meaning they would have had all their
numbers drawn.

Right now I have to do a visual inspection to see if there was a
winner. What I would like to do is, if the cells from C to L were all
yellow I would like to change the color

Is this possible?
 
You can add another Condition:

Select the cells that contain the entrants' numbers, e.g. C4:L401
Choose Format>Conditional Formatting.
To clear the current condition, click the Delete button
Check Condition 1, click OK
In Condition 1, from the first dropdown, choose Formula Is
Enter the following formula
=SUMPRODUCT(COUNTIF($M$4:$M$27,C4:L4))=10
(where 10 is the total numbers drawn, and
drawn numbers are in M4:M27)
Click the Format button, and choose a colour, click OK
Click the Add button
From the first dropdown, choose Formula Is
Enter your original formula, e.g.:
=COUNTIF($M$4:$M$27,C4)
where C4 is the active cell
Click the Format button, and choose a colour, click OK
Click OK
 
I tried that and the correct numbers were highlighted yellow but the
supposed winners were still yellow.

I was wondering if there is a way to do it with the "if" function.

Technically I would like it to do this:

=IF(AND(C4 has a background color of yellow,D4 has a background yellow,
up to L4 has background yellow,"Winner", No Winner"))

or:

=IF(AND(C4= list of winning numbers,D4=list of winning numbers, up to
L4=list of winning numbers,"Winner","No Winner"))

This function would go in Column M, and then use auto complete to
complete the rest of the Column. So that in column M it would tell me
if this row of numbers is a winner.

Doing this , there are 10 logical_tests. Can you do this many, and if
so , How do I write it?
 
Did you put the new condition in as condition 1? If you left the
previous one there, and added the new condition as condition 2, it won't
work properly.
 
I tried it that way but it didn't work, so I made a smaller version of
the pool and tried it and it didn't work either. I did this

- A B C D
1 2 3 4 2
2 4 6 8 6
3 5 7 9 9
4 2 6 9 1

D is the column with the winning numbers. The fourth row is the winner
and should be highlighted pink, and the rest of the winning numbers
highlighted yellow. I tried these conditions:

Condition 1 =SUMPRODUCT(COUNTIF($D$1:$D$4,A1:C4))=4
Format - Pink
add
Condition 2 =COUNTIF($D$1:$D$4,A1)
Format - Yellow
ok

I don't know if I am entering this correctly or not, but it didn't
work

If this isn't possible, is there a formula that can check to see if a
specific row has three of the four winning numbers?
 
With your small example, the formula for condition 1 should be:

=SUMPRODUCT(COUNTIF($D$1:$D$4,$A1:$C1))=3

and for Condition 2:

=COUNTIF($D$1:$D$4,A1)

where cell A1 is the active cell
 
Thanks Debra!!

I'm not sure how sumproduct works, but it did. Once again you saved m
a lot of work. Our ballteam sure would have hated me if we had paid
winner , only to find out later someone else had a share of the win
And with this formula in there, it would be hard to miss a row of pin
in a mass of yellow. With 27 of the 49 numbers out already, someon
should win tonight, so this was set up just in time.

Thanks again!

Dary
 
Back
Top