Conditional Formatting

[The Rook]

Apologies in advice for this being an easy one, but I seem to be having a
dumb day!

I have a conditional format in cell M17 which is a 'Use a formula to
determine which cells to format' =$M17<$K17 full red

This works fine.

I am then trying to copy this condition down several hundred lines, but it
copies it exactly as =$M17<$K17 rather than changing the row number each time
it is copied.

How do I copy it so that it changes the line number every time?

 

[trip_to_tokyo]

Take out the $ signs.

If my comments have helped please hit Yes.

Thanks.

 

[Gord Dibben]

Your references look good if exactly as posted.

Absolute column........relative row. Row should increment.

Is calculation set to automatic?


Gord Dibben MS Excel MVP

 

[David Biddulph]

The $ signs were on the column reference, not the row, so that doesn't sound
like the problem.

It sounds as if the OP is probably using Excel 2007, and I believe that 2007
probably gives confusing displays in this situation but does the right thing
(but I don't have Excel 2007 to try it out).
See a previous thread http://preview.tinyurl.com/y858u9s

There is some help on Excel 2007 CF at
http://office.microsoft.com/en-us/excel/HP100739391033.aspx?pid=CH100648451033#10

 

[Gord Dibben]

You are correct David.

In Excel 2007 the rows do not increment visually in the CF dialog when you
check the rule for each cell but the rule does increment in Excel's workings
somewhere.

It is confusing though to see the same rule in every cell.

Took a while to trust that it worked properly as far as formatting the
cells.............but it does.

I'm calling it a bug.


Gord