Excel 2007 Conditional Formatting problem - when copying cells?

Posted on 2011-10-03
Last Modified: 2012-06-27

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?



Question by:whorsfall
    LVL 18

    Expert Comment

    by:Curt Lindstrom
    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

    LVL 4

    Accepted Solution

    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: )

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now