As far as I know you can't with the normal Excel functions.

Depending on how the different cells became red you can think of making a normal or nested if function with the same parts in it as were given to make them red (highest of a series or lowest etc.). So if you have an example which didn't work, we can help you with that I think.

Another way is something I red about making your own function in VBA. It is possible to determine which cells are red and so you can make a calculation as well. I'm not sure if I can translate the function I red to English in a proper way, but maybe somebody else here knows already more about this?

Hi Paul,

You cannot identify the color of a Condtionally formatted cell, not even with vba. But you can evaluate the same formula used in that conditional format.

Iif you can upload a sample file with your rule, we'll help you.

Hi Catalin,

In the attached row 17 is the WINNER

Thanks

Paul

Hi Paul,

To clarify, columns J:S contain the winning lottery numbers and columns B:G contain the numbers selected by each person. For someone to win they would need all of their numbers to be present in any single week in columns J:S, not anywhere spread across columns J:S?

This is how the lottery normally works, but I'm not sure if that's what you're doing here. I ask because your Conditional Formatting turns a cell in columns B:G red if the number is present in any of the weeks in columns J:S.

Mynda

Hi Mynda,

Each person selects their own 6 numbers and these are put in columns B:G against their name.

Then each week the 6 numbers are entered in the box starting in column J, each week numbers are entered in in columns J:S unti the first person has all of their numbers highlighted, they then win and the process is started again

Regards

Paul

Hi Paul

One way is to use a COUNTIF to check each column from B:G to determine if the number is found in columns J:S.

If it is found, then it is given a value of 1 and so on. If the total value is 6, then we have a winner.

In your example there are actually 2 winners, row 17 and 61.

Refer to the attachment.

Hope this helps.

Sunny

Thank you so much, not only have you solved my problem but I have learnt something as well

Hi Paul

Glad we could help

Cheers

Sunny