Highlight Column of Prior Month

Hi

I hope that you are well, i am wanting to apply coditional formatting so that it highlights the prior month column based on a date at the top of the column.

I have the last day of the month in each column in Row 6 and i am wanting the spreadsheet to highlight the prior month based on this.

thanks In Advance

The Fish!

I don't want to have to replicate your data in order to help, so there are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Rule for C6:

=MONTH(C$6)=MONTH(TODAY())-1

Applies to: =$C$6:$O$21

C6 is the first cell in the range to which the rule needs to be applied. There are other ways, but MY preferred method is to set the rule in the first cell of the range, and then set the applies to range manually.

The rule for each cell in your range does this:

1. Look at row 6 and see if the date equals today's month minus 1 (so for May, this means April).
2. If the rule returns TRUE, apply the formatting to that cell.
3. The $ fixes the row, so all cells in the C range are looking at C6, all in D are looking at D6 and so on.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

No worries. For what it's worth, always be methodical about CF: if you try copying and pasting, you'll and up in a pickle. Follow my method and not much can go wrong. Been there, done that and got the T-shirt!!!