Excel 2007 Conditional Formatting problem - when copying cells?

Hi,

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:

=fnCheck_Data(A4,M4)

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

Thanks,

Ward




excel-image.bmp
LVL 1
whorsfallAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Regards,
Curt
Formatting.xlsx
pgorodCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.