Conditional Formatting based on formula not changing cells when copied to other cells NO $

[Throughtime]

I am using excel 2013 and a conditional formula based on a formula. The conditional formula is applied to cell A13 is: =OR(LEN(D13)>0,LEN(E13)>0,LEN(F13)>0) When these conditions are met, color the cell green otherwise leave it as is. Notice there are NO $ Dollar signs. However when I use the paint brush to copy the formatting to A14 to A30, they all are based on what happens on row 13, not row 15, 16, 17 etc. In the older versions if you based a conditional formula like this, it would change the formula you used based on the rows or columns you copied it to, unless you used the $ dollar sign to lock the reference. Is this a bug or what????

 

[DRSteele]

Conditional Formatting drives everyone crazy with the Absolute vs. Relative references. I'd suggest not using the format painter. Create the CondFormat in A13 just as you wrote it there, click Copy, select the other cells of interest in Column A and click Paste Formatting.

You see, you want the D13,E13 and L13 to be relative when the formatting is copied. In the CondFormatting screen it will show Applies To and show Absolute References.


If you have some spare time, watch these cool videos about CondFormatting https://www.youtube.com/watch?v=GRfe4bHsjhI

 

[Throughtime]

I tried that before I posted the question and that didn't even work. In previous versions I had no problem. Enter $ to hold onto the reference, no $ to float where it pointed. Now, that seems not to be the case. It should work, but doesn't with the paint brush or by just copying the cell and pasting formats. It still references the same cell locations as if a dollar sign was in the formula! AGH!!!!

 

[DRSteele]

But do the cells get properly formatted with green when they are supposed to? I bet they do - mine worked that way. And when I select any cell that is so formatted and look at the CondFrmt dialogue box, the formula says =OR(LEN(D13)>0,LEN(E13)>0,LEN(F13)>0) no matter which cell I have selected and the Applies to field says $A$13:$A$19 (in my test) no matter which cell I have selected. That looks weird but it works anyway.

 

[Throughtime]

That did it! Why it doesn't change the reference like the older versions of conditional formatting is crazy! I started out using Excel 1.0 that fit on an 800k floppy on a mac with no hard drive. One disk was the system disk and the other had the full program of Excel. Never have I had a problem with conditional formatting until now. Thanks for letting me know. It just seemed wrong as it was still referring to the original row but does seem to work anyway!

 

[DRSteele]

I'm glad your project can carry on. Why Excel does this is a complete mystery. Like I said, it drives everyone around the bend.