Conditional Formatting based on date range

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
37
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
=AND(E$2>=$C3,E$2<=$D3)
 

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
37
=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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,095
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,095
Office Version
  1. 365
Platform
  1. Windows
What do you see if you change E2 format to General?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,120
Messages
5,768,224
Members
425,460
Latest member
Astros1243

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