Conditional formatting & Vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jean-Paul Hahn - 23 Jan 2006 21:40 GMT

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

Reply to this Message

mrjsoftware@hotmail.com - 23 Jan 2006 22:08 GMT

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.

Reply to this Message

Jean-Paul Hahn - 24 Jan 2006 21:23 GMT

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.

> Use the COUNTIF() Function in Conditional formatting.
>
[quoted text clipped - 5 lines]
>
> then select the format you want to apply when the number is found.

Reply to this Message

Bob Phillips - 24 Jan 2006 21:27 GMT

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)

> Thank you very much for your tip. I had to adjust the formula to
>
[quoted text clipped - 18 lines]
> >
> > then select the format you want to apply when the number is found.

Reply to this Message

Jean-Paul Hahn - 24 Jan 2006 21:37 GMT

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

> You don't need the IF in CF
>
[quoted text clipped - 31 lines]
>> >
>> > then select the format you want to apply when the number is found.

Reply to this Message

Bob Phillips - 23 Jan 2006 22:15 GMT

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)

> Hi there
>
[quoted text clipped - 10 lines]
>
> Jean-Paul Hahn

Reply to this Message