Conditional Formatting

[ZombieHuckFinn]

I am trying to figure out how to set up a conditional formatting formula that will highlight values in a column if they are 179 days past TODAY(). I figured out how to do it for each individual column but there are 400+ entries and I assume a conditional formula will be the easiest route.

 

[Joe4]

Welcome to the Board!

If you figured out how to do it on a single cell, you should be able to apply that logic to a whole range of cells/columns. Just use the formula option in Conditional Formatting.

Chip Pearson did a nice write-up on Conditional Formatting a few years ago: http://www.cpearson.com/excel/cformatting.htm

If you get stuck, post back to this thread.

 

[abmati]

You can use conditional formatting with formula as under:
if date is in A column.
=TODAY()-$A1>=179

 

[ZombieHuckFinn]

So this is what I have for the formula:

=D2:D361-TODAY()>=180

I cannot figure out how to make it work for the range of cells. If the date is more than 180 days from TODAY() than I want it to format the cells that are >180 days within the Range D2:D361.

 

[Joe4]

Just highlight your whole range of cells, and write the the formula to work on the first cell in your range only. The rest of the formulas will adjust for all the other cells.

Likewise, you could just highlight and apply it to one cell, and use the Format Copier to copy the Conditonal Formatting to all other cells in your range.

 

[ZombieHuckFinn]

You're not teaching me to fish... you're throwing fish at me. When I use the two options I run into the problem I've had since the beginning of this post which is that with the conditional formatting the format painter and the other option you gave do not work. when I try those it highlights every single cell because it is using TODAY()-$D$2 as the only reference and not each cell in the range like it's supposed to.

 

[Matty]

What version of Excel are you using?

Matty

 

[ZombieHuckFinn]

2007

 

[Matty]

2007

Select D2, go to 'Conditonal Formatting'' > 'New Rule' > 'Use a formula...' > and in the white box use:

=TODAY()-D2>=180

Choose a suitable format via the 'Format' button and click OK.

Hit OK again, then use this in the 'Applies to' white box:

=$D$2:$D$361

Click OK again.

Matty

 

[ZombieHuckFinn]

Yes. However this still runs into the issue of using only the date in D2. I need it to use D2, D3... all the way to D361 so that each time it tries to format the cell it references the cell it's formatting, not just D2.