# Conditional Formatting

#### chriscbt

##### Board Regular
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.

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

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?

Should have been 19 but without the dollar sign it just lights up red no matter what value you have in M11

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

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)

SPOT ON...THANKS VERY MUCH

Replies
2
Views
90
Replies
2
Views
118
Replies
2
Views
269
Replies
15
Views
218
Replies
2
Views
134

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.

### Which adblocker are you using?

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

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