Conditional formatting & Vlookup

J

Jean-Paul Hahn

Hi there

Has any one a solution for validation lotto numbers.

In row 2 for example I have up to six numbers between 1 and 50 in colums 2
to 7

In rows 5 to 10 I have random numbers between 1 and 50 in colums 2 to 7.
For each cell I want to mark with a green background a match with the
numbers in row 2.

Thank you very much

Jean-Paul Hahn
 
M

mrjsoftware

Use the COUNTIF() Function in Conditional formatting.

If the 6 numbers are in Range B2:F2 and you have randon numbers in
Range B10:G15, then select the latter range, ener Conditional
Formatting fro mthe Format menu. Choose "Formula Is" and enter :

"=IF(COUNTIF($B$2:$G$2,G15)>=1,TRUE,FALSE)" (no quotes)

then select the format you want to apply when the number is found.
 
B

Bob Phillips

Select B2:G2 and in CF, use a formula of =COUNTIF($B$5:$G$10,B2)>0 and then
select your colour.
--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
J

Jean-Paul Hahn

Thank you very much for your tip. I had to adjust the formula to

=IF(COUNTIF($B$2:$F$2;B10)>0;TRUE;FALSE) for cell b10
=IF(COUNTIF($B$2:$F$2;C10)>0;TRUE;FALSE) for cell c10
=IF(COUNTIF($B$2:$F$2;D10)>0;TRUE;FALSE) for cell d10
=IF(COUNTIF($B$2:$F$2;E10)>0;TRUE;FALSE) for cell e10
=IF(COUNTIF($B$2:$F$2;F10)>0;TRUE;FALSE) for cell f10

and then copy/paste special, format to the other cells below.

Now everything works fine and I can quickly check wheter my numbers won in
the lottery.
 
B

Bob Phillips

You don't need the IF in CF

=COUNTIF($B$2:$F$2;B10)>0 for cell b10

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
J

Jean-Paul Hahn

Thanks Bob

You are right. Somehow, when I first tried it out it only worked with the IF
clause included. I don't understand why it didn't work correctly in the
first trial.

Jean-Paul
 
Top