Conditional Formatting Rule with Formula Applied to Multiple Cells

Hi,

I'm trying to apply a conditional formatting rule based on formula to a column of 100 rows. I create the rule for one cell and when I do copy-->paste/formats the formula in the conditional formatting rule remains the same for all the cells. Meaning: I need the formula in the conditional formatting rule to adapt automatically to the cell which it applies to.

The formula in B4 is: =TODAY()>B4+14

The formula in B5 must be: =TODAY()>B5+14

And so on and so for. It does not makes sense to me that I have to create a single conditional formatting rule for each and every cell in the column made of 100 rows (and maybe even more than that!). Please Help!!

Thank you,

Fabian

Hi,

On the conditional formatting window, do you have $ signs around B4, i.e $B$4?. If yes, remove these $ signs and copy the formula & it should work.

Hi!,

No, it still does not work as it should:

conditional formatting.PNG

Hi,

Please post the image in jpeg format, I am unable to view .png files.

There you are. Hope you can see it now:

conditional_formatting.jpg

Hi Fabi,

This is working normally. When you apply a condition to a range of cells, the formula shown is the one for the top left of the range. In your example, this is cell A2. This will display the same way regardless of which cell in the range you highlight before displaying the rule.

Hi Larena,

In other words, there is no easy way to solve this problem? I will have to manually insert a conditional formatting rule to each and every single cell in each and every single column, even if they are made up of 500 rows, for example?

So far the only way I know to deal with this issue is to paste the formats individually in each and every cell. Not a practical way, to say the least.

You don't have to it for each and every cell, you can use format painter or Copy Paste cells to copy the conditional formatting. Can you please post a sample excel file here? Use the following URL for steps on how to upload a file.

http://www.excelforum.com/members/da...ch-a-file.html

I think that I found out something: In a normal spreadsheet you can copy/paste the conditional formatting and it will work. But I'm unable to do the same in a pivot table range where you do not have even the option in the right click menu.