Conditional formatting of one row incorrectly applying to all rows

Hello,

I am creating a band song list spreadsheet in Excel 2007. The sheet contains one song per row with a status column per song that has a pull-down menu via validated data list of "Ready for Show", "Practicing", "Sidelined". I am using conditional formatting to fill the cell with colors of green (ready), yellow (practicing), red (sidelined). I defined that conditional formatting on one cell in row 5 (which works fine), and then copied the conditional formatting to the other rows. The problem is, when I change the status of the original cell/row where the conditional formatting was defined to "Ready for Show", it changes to green (correctly), but it also changes the colors of all other rows to green (incorrectly, since I only intend it to turn green when each row's status is set to "Ready for Show").

The formula in the conditional formatting where I created it on Row 5 is: =COUNTIF($D$5, "*Ready*")
When I look at the conditional formatting copied to row 10, it is: =COUNTIF($D$10, "*Ready*")

So it looks like Row 10 should operate independently as I intend, but the color for row 10 still changes color incorrectly according to the contents of Row 5 rather than Row 10.

I'm sure there's something I'm missing, but I've been struggling with this for a while so I'm hoping someone can help.

Thanks,
Peter

Originally Posted by martindwilson

kind of hard to guess ,post an example showing where this is happening

Sorry -- it didn't occur to me that I could upload the excel file. I have attached it. The cell where I created the conditional formatting is D5, I copied it to a few other cells (D2-3, D10, D27). All of those that were copied to take on the color of D5, where I intended to have them key off the contents of their own cell.

Hi mate,

I might have the solution for you here in the excel sheet I have attached.

I have deleted all your Conditional Formatting rules. Then I marked column D and set in the following 3 rules (you can always add more yourself based on the ones I made - otherwise contact me).

I used the following rule:

Please Login or Register  to view this content.

These rules will apply to the entire column D. If you need more help let me know.

True the =AND is not needed in the formula - but can we agree the solution I gave works?

Kagesen and Martin,

Thanks so much for your help. The modified solution does indeed work perfectly and learning that the copy of conditional formatting is cumulative (which is not intuitive, at least to me) helps me understand how to avoid this issue in the future. This is one of those things that I wouldn't have figured out in a long time without your help, so I really appreciate it!

Regards,
Peter