I'm having trouble with conditional formatting.

I have a spreadsheet for creating job codes. I want each line to appear in red and then when I change the cell in column I to 'Completed' it should all go black.

I can do this with Conditional Formatting easily enough - however the sheet has rows and I don't want to have to enter the CF function for every single one.

When I try copying and pasting it doesn't alter the cell numbers in the CF formula.

How can I make it repeat itself???