Pasting Conditional Formats, Relative, To Multiple Cells

[Solon Aquila]

Well, either Excel 2010 operates very differently from previous, or I have forgotten a huge amount about working with conditional formats.

I have two columns (J & K) with dates in them (Formatted mm/dd/yyyy).
If the date in column K is later than the date in column J, I want the cell to turn red.

I created a conditional format in cell K3 using the following formula:
=(K3-J3)>0

I tested it and found it worked. So I copied cell K3, highlighted a range of cells in column K (K4 through K10), and used Paste Special > Formats.

To my surprise, the relative references were treated as absolute in that ALL of those cells now have the conditional format formula that relies on cells J4 and K4. If I edit the rule I see:

=K4-J4)>0 is the formula and it "applies to" =$K$4:$K$9

I tried various things like using the Absolute sign ($), even used Ctrl+Click on the cells (Instead of click-drag), and no matter what I do, absolute or not, if I paste to multiple cells in the column and then check my conditional format it says that the range of cells use the data from just one cell.

I want the formula to follow the cell into which it was pasted but am at a loss for how to do this in 2010.

As a side note: if I highlight and copy from cell K3, then individually highlight and Paste Special > Formats to the cell K4, it works. Same for K5 and on down, but that means having to do this 350 times.

What have I missed?

Solon

 

[rafaelspeixoto]

I think this is just the way it shows in excel 2010. Doesn't it workfor the rest of the range?

 

[Solon Aquila]

I'm gathering that by 'the way it appears' you mean it LOOKS like the pasted cells are all 'looking at' the original cell, but perhaps they'll actually work the way I'm hoping?

If so, then no, it doesn't seem to work in the rest of the range. If I paste it into 10 cells, each cell relies on that first set of cells (J3 & K3) and they ALL change color if I mess with the dates.

I'm going to try rebooting.

 

[Solon Aquila]

Rafael,

Well, what a wonder rebooting can do! Thank you for asking that question, though, it looks like I understood what you were asking and that is the way it works.

Solon

 

[Solon Aquila]

OK, I'm back at Step 1 and rebooting hasn't fixed it. Neither has a Repair Office action. Using Office 2007 on Windows 7.

Problem: Conditional formats, when pasted, behave as though they're absolute references ($) when they aren't. (Side note: I tried making the fomula using the $, in case I had it backwards, and it still behaves the same way).

I'm able to copy, arrow-down-once, Edit > Paste Special > Format and have it work as long as I'm willing to repeat that for 500 rows.

SO, to make sure I have this right:
1. Start with cell C1 and put in conditional formatting so that if it's value is lower than that in cell B1, then C1 will change color.
2. My Conditional-Formatting formula is =B1<C1 to cause the color change. (Note there isn't any $, I think this means my formula is relative)
3. IN THEORY - If I highlight > copy cell C1 and then highlight cells C2-C500 and use Paste Special > Formats (or just paste if the cells are empty anyway), then the formula should increment the numbers so that cell C500's formula is "=B500<C500".

Is that correct?

It didn't change when I made the fomula =$B$1<$C$1, so I don't think that's it.

 

[hrb2001]

Did you ever find out what to do? I'm having this exact same problem.

 

[Solon Aquila]

I'm afraid it's been so long I barely recall that project. So I popped open my Excel and tried it and I'll be ****ed if it doesn't BEHAVE like I want it to (cell turns the color it's supposed to when it's supposed to do so), it just doesn't LOOK like it should have worked.
And by that, I mean all of my conditional formatting makes the cell the right color, just like I want.. but when I manage/edit the formula for one of those cells, the formula itself shows incorrect cell numbers and the like.
SO.. it functions properly.. just doesn't look like it's supposed to.
In the end, I just accepted that the formula would look wrong but function properly.
Sorry I can't be of more help on it.
My suggestion is to do whatever makes it run the way you want it to and go from there.
PS - YOu've come to the right forums. This group is a great bunch of people and extremely helpful/informative!