Using Countif in Conditional Formatting with Sequential Cells

Question

Hello,

I have an excel sheet which I need to highlight rows based on value from another cell. If this particular cell is part of a set of certain numbers, the entire row gets highlighted. Easy enough to do with conditional formatting with a COUNTIF formula.

My problem is when I move to the next line. How do I get the formula to look at the next cell without having to rtype it over X00 times? Like if =COUNTIF($AF$4:$AF$341,$AD4)>0 is my formula, how do I get it to move from $AD4 to $AD5 for the 5th row?

Answer 1

You might be able to use the format painter.

Answer 2

Andrew, copy and paste will take care if it. Suppose your formula is in BA4, copy it and paste it into BA5. Excel will automatically change the formula with $AD4 in the cell BA4 into the formula with $AD5 in cell BA5.

if you have any questions, please ask.