Use EDATE in your CF formula.
Select your range of dates (let's say B2:B20).
Go to
Conditional Formatting →
New Rule →
Use a formula to determine which cells to format.
Enter this formula:
Use the
Format button to choose the highlight you want (font colour, fill colour, etc).
With the range still selected, repeat with this formula:
Choose your other highlight.
Make sure you do them in that order. If you go to
Conditional Formatting →
Manage Rules, you should see the
<12 months rule at the top.
The cell reference in the formula should be the top-left cell of your range - Excel will automatically change it for the rest of the range you have selected.
If you need to expand the range later, do it using
Conditional Formatting →
Manage Rules. You'll see the
Applies To range for each rule there.
Hope that helps.
Bookmarks