Conditional Formatting

chriscbt

Board Regular
Joined
Jul 14, 2005
Messages
91
Ok, I'm trying to be clever with a lottery syndicate sheet

What I have currently is a list of our lottery numbers in excel and a list of the winning numbers (we have 35 lines of lottery numbers)

I'd like to make it so that when i enter a winning lottery number in a specific cell (M11, M13, M15, M17, M19, M21) then the cells with our lottery numbers in will go red, i can't use conditional formatting as i cant use a range or more than three functions on one cell.

Can anyone help me get round this..?

Chris
"A problem is just a solution waiting to happen"
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this in your conditional formatting dialogue.

Choose formula is from the drop down,

enter: =CHOOSE(A1,$M$11,$M$13,$M$15,$M$17,$M$19,$M$21), where A1 one of the cells where you enter the lottery numbers, then simply Copy and Paste Special|Formats to all other cells that you want condition to apply to.
 
Upvote 0
yeah, i just tried that but i cant get it to work.

I've used =CHOOSE(E11,$M$11,$M$13,$M$15,$M$17,$M$27,$M$21)

in the conditional formatting for cell e11.

HELP.

Regards

Chris
 
Upvote 0
Try again without the dollar signs and let me know.

Also note: Your last example include M27, your original was M19, could that be a problem?
 
Upvote 0
ADVERTISEMENT
Should have been 19 but without the dollar sign it just lights up red no matter what value you have in M11
 
Upvote 0
It's working perfect for me,

Your conditional format is on E11 right? And the cells you want to compare are in the M cells?

I am sure there are other ways....let me think...


You have Formula Is selected from the drop down of the CF dialogue?

If you look at the conditional formatting formula again, is it surrounding by quotes? If so, delete them
 
Upvote 0
This is more brute force but,

=OR(E11=$m$11,E11=$m$13,E11=$m$15,E11=$m$17,E11=$m$19,E11=$m$21)
 
Upvote 0

Forum statistics

Threads
1,196,073
Messages
6,013,273
Members
441,759
Latest member
ab_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top