conditional formatting question

hey guys, new to site!

i have a pick list for NFL survivor pool, where i enter each participant pick....

i have condition formatting, where it highlights cell green if team is correct (=MATCH($C4,'MASTER SCHEDULE'!$D$2:$D$17)....using data from Master schedule...

question is, how do i copy conditional formatting to cells below (C5 to C23) without having to keep editing the formula?

also how would i go about highlighing RED when entry is wrong? is there an opposite to MATCH formula?

file has been attached,

thanks in advance

What verson of Excel does this have to work in?

Are you using Excel 2010?

Try this...

On the Pick sheet...

Select the *entire* range C4:S23 starting from cell C4.
Cell C4 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Apply the conditional formatting for a win.

Formula:

=AND(C4<>"",MATCH(C4,OFFSET('MASTER SCHEDULE'!$D$2,MATCH(C$2,'MASTER SCHEDULE'!$A$2:$A$257,0)-1,,COUNTIF('MASTER SCHEDULE'!$A$2:$A$257,C$2)),0))

Apply the conditional formatting for a loss.

Formula:

=AND(C4<>"",ISNA(MATCH(C4,OFFSET('MASTER SCHEDULE'!$D$2,MATCH(C$2,'MASTER SCHEDULE'!$A$2:$A$257,0)-1,,COUNTIF('MASTER SCHEDULE'!$A$2:$A$257,C$2)),0)))

Originally Posted by fivestar17

Can you try emailing to [email protected]

Done... Hmm seems every file sharing = Poo! Today.

For SkyDrive, you can't SaveAs, don't have my permission to save files on my SkyDrive. Have to select SaveAs first, then you get an option of SaveAs and Download, you need to select download. Don't know if that is what you did.

Instead of using drop down lists have a look at this...

http://www.excelforum.com/excel-prog...ent-macro.html

That is soooooooo much easier and faster than using drop down lists.

You're welcome. Thanks for the feedback!