Conditional formatting & Vlookup

[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

[[email protected]]

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.

[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)

"Jean-Paul Hahn" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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.


<[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> 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.
>

[Jean-Paul Hahn]

Hi Bob

Thank you very much for your tip.

I had to adjust the formula for the conditional formatting 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.



"Bob Phillips" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> 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)
>
> "Jean-Paul Hahn" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

[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)

"Jean-Paul Hahn" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> <[email protected]> schrieb im Newsbeitrag
> news:[email protected]...
> > 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.
> >
>
>

[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

"Bob Phillips" <[email protected]> schrieb im Newsbeitrag
news:%[email protected]...
> 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)
>
> "Jean-Paul Hahn" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>> <[email protected]> schrieb im Newsbeitrag
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>
>