Conditional Formatting based on date range

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
46
I have the following table. For each row, I would like it to highlight the cell if the column header (E) is greater than or equal to the value in Column C and if it is less than or equal to the value in Column D.

For instance, if we looked at Apartment Investment Advisers, E3 would have no highlight, F3 would, G3 would, H3 would.

Impress Usage.xlsx
ABCDEFGH
1Subscription
2CustomerSales RepMonth15th Month2020/102020/112020/122021/01
3APARTMENT INVESTMENT ADVISERSBryan Schweet2020/112022/01$ -$ -$ 539.85$ 2,004.75
4BROWN & BROWN OF OHIO LLCRandy Ratliff2020/102021/12$ -$ 144.25$ 395.23$ 550.29
5CITY OF LEON VALLEYEddie Lopez2020/102021/12$ -$ -$ 12.50$ 49.97
6GOOD KARMA BROADCASTING LLCTamera Dodge2020/112022/01$ -$ -$ 339.55$ 276.57
7INDIANA LOGO SIGN GROUPWallace Sisemore2020/102021/12$ -$ 36.48$ 197.60$ 189.22
8IRALOGIX INCMarilyn Shaffer2020/102021/12$ -$ 9,878.90$ 764.70$ 923.27
9LEADER PRINTING AND MAILING LLCStephen Havens2020/102021/12$ -$ 0.12$ -$ -
10MFI CREDIT SOLUTIONS LLCKevin Moody2020/102021/12$ 2.01$ 3,959.78$ 2,851.78$ 5,917.24
11NEW DREAMS INCStephen Havens2020/122022/02$ -$ -$ 114.56$ 119.25
12PRECISION RISK MANAGEMENTMark Sohn2020/102021/12$ -$ -$ -$ 2,484.22
13R I LAMPUS COMPANYAmanda Davis2020/092021/11$ 0.08$ 227.90$ 160.16$ 117.86
14REPSOL OIL & GAS USA LLCMarilyn Shaffer2020/112022/01$ -$ -$ -$ 0.36
15SBC WASTE SOLUTIONSBryan Schweet2020/122022/02$ -$ -$ 109.42$ 637.64
16STAMPER & COMPANY INCStephen Havens2020/122022/02$ -$ -$ -$ 0.74
17TEXAS DISPOSAL SYSTEMSPeter Soto2020/102021/12$ -$ -$ -$ 0.18
18
19$ 2.09$ 14,247.43$ 5,485.35$ 13,271.56
Main
Cell Formulas
RangeFormula
D3:D17D3=EDATE(C3,14)
E3:H17E3=VLOOKUP($A3,Pivot!$A:$T,COLUMNS(Pivot!$A:B)+1,0)
E19:H19E19=SUM(E3:E18)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
=AND(E$2>=$C3,E$2<=$D3)
Here's what I get when I use that:

Impress Usage.xlsx
ABCDEFGH
1Subscription
2CustomerSales RepMonth15th Month2020/102020/112020/122021/01
3APARTMENT INVESTMENT ADVISERSBryan Schweet2020/112022/01$ -$ -$ 539.85$ 2,004.75
4BROWN & BROWN OF OHIO LLCRandy Ratliff2020/102021/12$ -$ 144.25$ 395.23$ 550.29
5CITY OF LEON VALLEYEddie Lopez2020/102021/12$ -$ -$ 12.50$ 49.97
6GOOD KARMA BROADCASTING LLCTamera Dodge2020/112022/01$ -$ -$ 339.55$ 276.57
7INDIANA LOGO SIGN GROUPWallace Sisemore2020/102021/12$ -$ 36.48$ 197.60$ 189.22
8IRALOGIX INCMarilyn Shaffer2020/102021/12$ -$ 9,878.90$ 764.70$ 923.27
9LEADER PRINTING AND MAILING LLCStephen Havens2020/102021/12$ -$ 0.12$ -$ -
10MFI CREDIT SOLUTIONS LLCKevin Moody2020/102021/12$ 2.01$ 3,959.78$ 2,851.78$ 5,917.24
11NEW DREAMS INCStephen Havens2020/122022/02$ -$ -$ 114.56$ 119.25
12PRECISION RISK MANAGEMENTMark Sohn2020/102021/12$ -$ -$ -$ 2,484.22
13R I LAMPUS COMPANYAmanda Davis2020/092021/11$ 0.08$ 227.90$ 160.16$ 117.86
14REPSOL OIL & GAS USA LLCMarilyn Shaffer2020/112022/01$ -$ -$ -$ 0.36
15SBC WASTE SOLUTIONSBryan Schweet2020/122022/02$ -$ -$ 109.42$ 637.64
16STAMPER & COMPANY INCStephen Havens2020/122022/02$ -$ -$ -$ 0.74
17TEXAS DISPOSAL SYSTEMSPeter Soto2020/102021/12$ -$ -$ -$ 0.18
18
19$ 2.09$ 14,247.43$ 5,485.35$ 13,271.56
Main
Cell Formulas
RangeFormula
D3:D17D3=EDATE(C3,14)
E3:H17E3=VLOOKUP($A3,Pivot!$A:$T,COLUMNS(Pivot!$A:B)+1,0)
E19:H19E19=SUM(E3:E18)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:H17Expression=AND(E$2>=$C3,E$2<=$D3)textNO
 
Upvote 0
It looks as though the values in E,G & H row 2 are not dates but text.
It would be simplest to convert them to dates.
 
Upvote 0
What do you see if you change E2 format to General?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top