Excel 2007 Conditional Formatting problem - when copying cells?


I am using conditional formatting in Excel 2007.

Specifically "Use a formula to determine which cells to format".

I have filled in the dialog "Format values where this formula is true:"

In this dialog it says: =fnCheck_Data(A3,M3)

I am formatting the contents of cell M3 and referencing cell A3.

The problem is when I copy this cell references stay constant.

What I was hoping would happen is these cell references in the formula
adjust to for the cell below the formula would change to:


How can I achieve this? What is correct way to copy the result to multiple
cells so it does what I want?



Who is Participating?
Once I had a hard time with Conditional Formatting and it took me a long time to get the answer...

It is not exactly the problem you describe - in fact, I find your problem strange, I do that all the time, copying cells with conditional formatting that adapts cell references (unless I lock with $).

But my "finding" could shed some light on your problem. It is simply this: the active cell also affects the way Excel handles conditional format formulas! This is not an obvious thing, because if you specify the "Applies to" range, you don't expect the active cell to be of any importance, but it is.

The formula you type, and the way it copies over to other cells, will be interpreted in reference to the active cell.

(you can read more about it here: http://dmcritchie.mvps.org/excel/condfmt.htm )

I'm sure by playing around with this you will see different results and eventually solve your problem. And if you don't understand all this, simply try placing the cursor in cell "A1" before entering any conditional formatting formula - this is usually enough to clear confusions.
Curt LindstromCommented:
When you copy the cells downwards the formula remain displayed as first entered but the formatting should work as you require it to work. See attached file where the formula is entered as =A1=C1 and then copied downwards in column A

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.