Conditional Formatting - I'm stumped!

Hi all,

I don't know if I'm trying too hard or just have a fried brain at this point. Any help will be greatly appreciated. I have a spreadsheet spanning 6 months. In the comments column I have 4 conditions that if met the cell will fill with a color to match the comment. My problem is that when I try to copy this to the next cell rather than manually enter it, the copy includes all cells. So rather than having C1 with cf (a=blue fill, b=green fill, etc) then C2 the same, it changes to C1:C2 with cf (a=blue fill, b=green fill, etc). Is there a way to copy and have each cell stand alone using the conditional formatting?

I originally set the cf in N2 then copied each cell all the way down through the 7/5/13. I then copied all cells from 7/1/13 - 7/5/13 for the following week. When I checked the cf in the cells, this is what I found even in N2

$N$2:$N$29,$N$31:$N$45,$N$47:$N$52,$N$54:$N$62,$N$64:$N$98,$N$100:$N$127,$N$129:$N$143,$N$145:$N$150,$N$152:$N$160,$N$162:$N$196

I can't count how many times I've fixed this only to have it come back. Does anyone have any suggestions?

try this

goto conditional formating
Formula

New rule

N2="Special Order"
then format blue
OK
then when you get to Applies to
change to $N$2:$N$200

repeat for other fills

see attached- i have added all the 4 conditions
you may need a validation so that only those words can be entered - otherwise you may get
Powershipped
or
Power shipped
and the latter will not work with the space

$N$2:$N$29,$N$31:$N$45,$N$47:$N$52,$N$54:$N$62,$N$64:$N$98,$N$100:$N$127,$N$129:$N$143,$N$145:$N$150,$N$152:$N$160,$N$162:$N$196

is the correct syntax, that gibberish is saying that those ranges are the ones that will respond to the condition of ie "Special Order", it is such a long weird string because you excluded the black rows, etaf's suggestion includes the black rows. and even a few rows after the last black row.

etaf's suggestion is a good fix though because you won't be typing in the black rows anyway so there will be no conditional formatting applied, etaf is also correct in saying that you should use data validation dropboxes or something so people don't input a variation of the conditions as stated in etaf's post

Thanks so much. This does work I was just trying to make it a single cell at a time rather than a range of cells. This does make more sense though. Like I said, my brain was fried (probably because I was focusing on something I didn't need to be focusing on). Thanks again for the help!!