Automatically change the fill color in a row based on date range in other columns

[FullertonRegan]

I have a sales wins spreadsheet that details the dates a sale commission begins (column M) and commission ends (column N). All commissions begin and end on the first of the month, so all dates are formatted as 1/1/13, 2/1/13, 3/1/13 etc. in each column. We then have additional columns with the months of the year in which we enter the commissions from that month (column O-Z has January 2013-December 2013), with a total column afterward on column AA. 2014 and so one are in the next respective rows each with a total column at the end of the year. My question is, what can I do to automatically highlight the cells in Columns O-Z if they fall in the date range specified in columns M & N (commission begin & end dates)? For example, we win a new client in January 2013 and begin getting commission in February 2013. I would enter the date "2/1/13" in column M and "1/1/14" in column N as commissions last one year. How can I format my workbook so that all of the cells in this row from February 2013 through January 2014 are highlighted? I'd also need to skip the total columns. Conditional Formatting doesn't work in this situation unfortunately, so I would need to do a macro I assume? Any help is greatly appreciated! Thank you!!

 

[RonB1111]

Here's how to do it with conditional formatting:

1. Your date header in Col O-Z needs to be 1/1/13, 2/1/13, etc (you can format the cells for display purposes as dddd yyyy in the FormatCells-NumberTab-Custom-Type box so it displays as January 2013, etc)

2. Select cells O2 thru Z1000 (or whatever you expect your last row of data to be)

3. Click ConditionalFormatting-NewRule and then click "Use a Formula to determine which cell to format"

4. Enter this formula in the box under "Format values where this formula is true":

=AND($M2<=O$1,$N2>=O$1)

5. Click the format button and on the fill tab select the color of your choice, then OK, OK

 

[FullertonRegan]

Thanks Ron! That worked perfectly. I never knew about that function in Conditional Formatting and I can see how it will open many doors for my formatting in the future. I really appreciate the help!

 

[RonB1111]

You're welcome. Thank you for providing feedback.