I actually went back and got it to work. I even found that the cells correctly retained their formatting when I re-sorted the data.

Now my question is, how do you copy/paste the conditional formatting rules into other rows? The dollar sign stops the formula from dynamically changing when you paste it into another row. So that means having to painfully replace each row number on every line.

To clarify, each cell in the A column will require multiple rules. I can't remove the dollar sign to drag the rules down. Copy/pasting doesn't work without editing the row numbers in the Edit Rule dialog. Using the formatting paintbrush just copied the attributes of whatever background color was in the cell.



Is there an easier way? Thanks!

to apply the rule to several rows, first select the whole range you want the rule to apply to, then create the rule with the correct formula. If you want the rule to aplly to an extended range, you have to edit the rule, you cannot copy the format; under conditional formatting-manage rules-edit rule-change the range for this rule. to apply the formula to all rows in range, the formula should look like: =$A9="p" ,(notice that before row number there is no $) . If you put the $ sign in front of the row number, the format will be applied only to row 9. Look at the sample attached. you can check this thread for similar problems: https://www.msofficeforums.com/excel...ence-data.html

Attached Files

sample.xlsx (9.1 KB, 21 views)