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

FullertonRegan

New Member
Joined
Mar 29, 2013
Messages
2
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!!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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":
Code:
=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

New Member
Joined
Mar 29, 2013
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top