Format painter - copying & painting conditional formatting formula

[mykeee]

I am having trouble with the format painter. I have a cell that I have applied conditional formatting to and would like to copy that formatting to several cells below (in the same row). The conditional formatting I have placed on the cell DN6 contains a formula:

=DN6< DN$3

So basically if the value (date) of cell DN6 is less than the value (date) of cell DN$3 the formatting will be applied (turn cell red). I tested the formatting in DN6 and it works as I want, but now I want to copy and paint that formatting to cells DN7:DN395. What happens is that the formula in all cells from DN7:DN395 is the same:

=DN7< DN$3

I would like to first part of the formula to change to DN7, DN8, DN9...etc, but it is always DN7. If I paint one cell at a time the cell reference changes as I would expect, but doesn't if I paint multiple cells. Any idea how I can get this to behave the way I expect it to?

 

[mykeee]

Grrrrr! Why isn't this forum letting me post the formula?! It is deleting the last portion of the formula.

The formula I applied in cell DN6 is:

= DN6 < DN$3 (without the spaces)

When I tried painting the formula to multiple cells in the same column, starting in cell DN7 the formula is always:

= DN7 < DN$3 (without the spaces)

 

[RoryA]

I think you've misunderstood what the dialog is showing you (it's really not clear). The formula is shown as it applies to the first cell of the range to which it has been applied. So if you apply it to say DN6:DN395, when you select one of the cells and view the CF dialog, it will always show the formula as =DN6< DN$3 and the applies to range as DN6:DN395. This is correct - the first row number will adjust for each row as required.

 

[mykeee]

I understand that the "Applies to" is telling me what cells the formatting has been applied to, however the formula is not changing from cell to cell. For example once I have painted the formatting to cells DN7:DN395 and as an example I check the conditional formatting in cell DN13, I see the following:

Applies to:
= $DN$7 : $DN$395

but the formula still says:
= DN7 < DN$3

It should be:
= DN13 < DN$3

...shouldn't it?

 

[RoryA]

No because as I said it shows you the formula relative to the first cell in the applies to range, not relative to what you have selected. It's not at all intuitive in my opinion and I much prefer the way it used to work in 2003 and older!

The actual CF should be working as you need it to though!

 

[mykeee]

Right you are! Thanks! Yes it is very confusing and misleading...