Conditional Formatting when Adding Rows

Question

I have a spreadsheet that has conditional formatting in Column D (to have the conditional formatting only apply to that cell if something shows up in Column X of that row).

I have 38 rows that have the same conditional formatting.  However, if I add rows to the spreadsheet, say between rows 55 and 56 the conditional formatting changes to

How do I get the conditional formatting to only apply to the single cell for that row as it does in the cell above?  There are not very many times that we have to add rows to the worksheet but the times we do I need it to be correct so someone does not have to go in and change the formatting for each cell.  Any help would be greatly appreciated.  Thank you.

Kim Krebs

Answer 1

You should base the conditional formatting rule on a formulas like,

=$X2=123

... and make sure that the Conditional Formatting Rules Manager shows an Applies to: range of something like,

=$D$2:$D$39

Finally, make sure that the rule has not been copied to other cell ranges by accessing Show formatting rules for: This worksheet. Conditional formatting rules get copied and pasted into other cells much as conventional cell or cell number formatting does.

If you can be a little more specific about 'if something shows up in Column X of that row', I may be able to offer more precise directions.

Answer 2

Thank you very much.  How you explained it above made sense.  I changed my conditional formatting on my sheet and it works the way I want it to.

Thanks again.

Kim Krebs