conditional formatting

[Guest]

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

 

[Guest]

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH

 

[Guest]

..... SUM Condition is not UNIQUE! (Dooh!) so I'll give some more thought!

Sorry!

 

[Guest]

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

 

[Guest]

For matching a line try:

Formula is:

=AND(MATCH(B1,Lotto,0),MATCH(C1,Lotto,0),MATCH(D1,Lotto,0),MATCH(E1,Lotto,0),MATCH(F1,Lotto,0),MATCH(G1,Lotto,0))

HTH

 

[Guest]

Hi
I've cracked it...all you do is put the numbers drawn each week in a row to
one side. then -as you said- highlight a row of numbers against a person's
name and the conditional format using:-
=Match(B1,draw,0) where 'draw' is the row of cells (i used 50 cells as
there are only 49 numbers).
For the "Numbers drawn" square - again use the same formula.
You then use 'Format Painter' and apply to all the numbers in the rows.
Works a treat.
All I wanted was a kick and it got what I wanted,,,Thank You Very Much...

Lofty

 

[Guest]

Glad it's all working. If YOU win, remember me!

Hi
I've cracked it...all you do is put the numbers drawn each week in a row to
one side. then -as you said- highlight a row of numbers against a person's
name and the conditional format using:-
=Match(B1,draw,0) where 'draw' is the row of cells (i used 50 cells as
there are only 49 numbers).
For the "Numbers drawn" square - again use the same formula.
You then use 'Format Painter' and apply to all the numbers in the rows.
Works a treat.
All I wanted was a kick and it got what I wanted,,,Thank You Very Much...

Lofty

 

[Guest]

Sorry - but as the runner I'm not allowed to enter!......But if you're on
Anglesey come to Y Bedol