I would like all cells in a range to become formatted blue when the first cell in the row contains "p". However, I can't seem to get it to work. I went to the Conditional Formatting Rules manager, and the rule is: Formula: = "a9=p". The Format field contains black text on a blue background, and the Applies To field contains the range of cells: =$A$9:$H$9.



When I change the contents of A9 to "p" nothing happens. I also tried an IF statement, but that didn't work either.

I also presume I'll have to remove all the $ from the "applies to" field because I'll be re-sorting the data in a number of ways, and would like the formatting to follow the content when it re-sorts. However, when I attempt to do this, Excel puts the $ back.

Could you please tell me how to enter this so the formatting works correctly?

Thanks!

I looked at things more closely, and apparently the functionality doesn't extend that far.

The correct formula for what you need is =$A9="p" .

Thanks! That worked in the A9 cell, but it didn't format the other cells in the row no matter how or where I entered the formula.

In my spreadsheet, the character in the "A" cell determines the formatting for that row. Up until now I've been formatting the color manually. But I wanted to assign a conditional formatting formula with a different background color for each of the characters that might appear in the "A" cell. I should think it would work - it's just a command - but it doesn't. It may be a bug. Or my logic may be incorrect in figuring out how the feature is supposed to work.

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, 19 views)

Thank you so much! It worked perfectly!