Conditional Formatting, copying it down.

Question

Afternoon

I have conditionally formatted in row 3, and I want it to copy down but change the reference each time (so instead of looking at B3 for row 3, it will look at B4 for row 4 etc). How do I do this? Because every time I've tried, it still takes reference from row 3.

Please see below a link to the spreadsheet:

https://1drv.ms/x/s!AldNdi8CoTHPkUER72AN4GMmz-PG?e=pwgRp0

Thanks in advance.

Hannah

Answer 1

Hi Hannah. I am an Excel user like you.

The issue you are having is because Excel will sometimes make the cell reference, in this case B3, an absolute reference so that all cells down the column will look at B3 rather than the cell in the current row. This is the same principle as when a formula is drug down, absolute references do not change. See the illustration below.

Go into the Conditional Formatting menu and choose Manage Rules to see the window above. You can see where the formula is circled that the $ signs are there to make it an absolute reference. Click on Edit Rule, remove the dollar sign from the row number in the formula (=$B3="a"), and click OK in order for the conditional formatting to be able to change and refer to each successive row in the range to which it is applied.

This will take you back to the window above. You can then expand the range in two ways;

1. Click OK and use the Format Painter as Dave described to expand the range. Or...
2. Select and delete the Applies to range in the window above and then drag/select the entire range you want the conditional formatting to apply to.

Because we left the $ sign in front of the column reference we can also select multiple columns in the range which will highlight all of the cells in the row for those columns included in the range.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Hi Hannah

I am Dave, I will help you with this.

Create the Conditional Formatting in the top cell in that column

Select that cell and press Ctrl + C to copy the cell

On the Home tab in Excel, click the Format Painter button

Click and drag downwards form the second cell in the column to fill with the appropriate Conditional Formatting rules.