Relative reference in conditional formatting

[jeffcp66]

I am trying to apply a pattern to multiple cells when a date is entered in one particular cell. I need to use relative reference because the block of cells needs to be copied and pasted as needed.

In the attached, the vertical line pattern is present because the formula in conditional formatting is:

Please Login or Register  to view this content.

Note that the COPY of these cells does NOT have a date in L18, so the lines should not be there. But they are, of course, because the cell reference $L$6 is absolute. But if I just use L6 when entering the conditional format with all cells selected, the formatting does not work except in one cell.

The only way I can find to make this work is to enter =ISNUMBER(L6) in EVERY single cell, individually. I have tried $L6 and L$6 and neither work.

Is there any other way to do this? I have multiple sections like this, all of which need to be copied and pasted often. So to enter the formatting for each cell individually would require a few hundred entries.

(Note: the copy and pasting function in my spread sheet is performed by a macro. So if there is an easy way to put this in the macro code, I'm all ears.)

[jomaor1]

Hi,

So for me, it actually worked with "$L6", you just need to edit every single ROW to that, and not every single cell.
So when you copy down, every Row will bring the 6 down to 18. (They always sum the number of cells you're going down).

Try changing the conditional formattings to be based by rows, and not cell by cell.

[jeffcp66]

Thanks! That helps a lot. I'm guessing that may be the best I can get, which requires only a handful of entries rather than 30 or 40 per template.