* Select B2.
* Conditionally Formatting>Manage Rules
* For applies to, you have just =$B$2 for each rule.
* They should apply to all the cells in your table in B, i.e. =$B$2:$B$67

Easy peasy. Keep the $s in the apply ranges. More on that later.

* Select the first rule.
* Click Edit Rule.
* The equation as copied is =TODAY()>DATE(YEAR($A2)+1,MONTH($A2)-1,DAY($A2))
* Change all $A2 to $B2.
* Click OK.
* Repeat for the remaining rules.

The reason this happened is because I left some absolute reference markers, $, in the equations. I only removed the $s in front of the row reference in case you wanted to copy the formatting down. I didn't consider you would be copying across (from A to B). Thus, when you copied the equations from A in the sheet I posted to B in your sheet, the formulae did not change likewise. My bad.

I took care of it for you, but you might want to go through this on your own as an exercise to understand how this works.

And here's a bit of homework for you to hone your conditional formatting skills. There is one date, 4/1/15, that has no formatting. I suspect that this is because its anniversary date is more than a year away. See if you can modify the "green" rule to accommodate this date.

Attached Files

Working Sheet2.xlsx (13.3 KB, 21 views)