Conitional Formatting - Absolute refference will no delete

daveryandewey

New member
Joined
Feb 21, 2013
Messages
2
Reaction score
0
Points
0
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

=ISBLANK($G39)

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.....
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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
 

daveryandewey

New member
Joined
Feb 21, 2013
Messages
2
Reaction score
0
Points
0
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.....
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top