Use PasteSpecial.ConditionalFormats. Or select the whole range you want to apply the conditional format to at start. And be sure you have absolute and relative reverences set properly (defining conditional formatting, the formula is written for topmost selected row, and the rule is copied down by excel like ordinary formula). Usually having row reference relative is enough.