Results 1 to 4 of 4

Thread: Conitional Formatting - Absolute refference will no delete

  1. #1

    Conitional Formatting - Absolute refference will no delete

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I have created my conditions and am now trying to copy them to the next rows.

    The sheet will always be added to in the future so I do not want to use absulote refferences. I can go through the rows I have now but this will not assist me with future row insertions.

    All my other formulas seem to work fine:

    =AND($G39<>"", TODAY()-$G39>25) - Turn row amber if date exceeds 25 days from current date

    =AND($G39<>"", TODAY()-$G39>30) - Turn row red if date exceeds 30 days from current date

    =$L39="In-Progress" - If marked as In-Porgress - do not format the row

    =$L39="Completed" - If marked as Completed- do not format the row


    I can copy the format to each row and then currently have to amend the applies to range. However when I try to amend the "Applies to" field for each condition (So that the refference is not absulote) it will not remove the $ refference. I have tried using F4, removing all formatting and starting again. I have also tried various ways to create - i.e highlighting the row and then using the CFR manager.

    Please help as I am going mad.....

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Brussels Belgium
    Excel Version
    2010 on Xubuntu
    If you mean all these conditions apply to a single cell A1,say, all you have to do is apply them to the entire column instead of a single cell
    If you are using 2007 or later go to CF - Manage rules and change the scope of each rule ( text box under "applies to" from A1 to say $A$1:$A$500 or whatever range you need).
    You do not need to apply the rules to each cell individually

  3. #3
    Thanks for the quick response - My formats relate to the entire row - =$A$3:$M$3 - I am trying to make the condition =$A7:$M7 to allow me to copy the format - However, no matter what I do the "Applies to" field reverts the input to the absulote condition.....

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    As said, you don't need to format one row and then copy to others, just go into the CF and change the range it applies to to cover your new rows. Excel will take care of the formula condition s, it is those that need to be not fully absolute, not the applies to range.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts