Complex conditional formatting involving varying ranges

Hi guys,
We have a document based on the lottery, "LOTTO"
All those names have 10 numbers, from Colum D to M.
Now i use conditional formatting to color numbers that match.

To explain how everything works:
Each 3 days there will be a lotery with 6 numbers.
U can find the current numbers in colum D196 till I196 (thats one lotery)
The other lotery's are lower from D 197 till I197 and so on.

Now what do we need to have?
If i enter the 6 numbers from D202 Till I202, they have to be colored in red "background" with the matching cells in range D2:M154
Now this works with conditional formatting, but as final work, i need to count how many numbers the participants need to have them all. "Colum P"
I need to count how many numbers they still need to complete their winning.
Example, MAGDA P in B2 needs only 1 number, number 34 in I34.
So this needs to be calculated in Colum P .... Counting range D2 till M2 minus 10 numbers....
That should be no problem, the only problem now is, that conditional formatting is not allowing u to connect with VBA or standard formules....
If i use CF it will count the range as not coloured.... because CF is laying on top of that
Anyone has a proposal for this?
Maybe a new way to light the cell up in VBA?


Project example:
ftp://esekk.dyndns.org:1337/Lotto.xls

Thanks in advance....

Hi, and welcome to the forum.

Unfortunately Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.



Since you are new here I'll change it for you on this occasion, but please note for the future.