Conditioning Format if Criterias are met and to fill across columns of Dates based on start and end dates

lewjan

New Member
Joined
Dec 4, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
Hi,
Thank you in advance for your help and time.

Been trying for a while and can't seem to get the conditional formatting to work; grateful if you can assist.

Under the store column Whole = green and retail = red
If the criteria listed under the "store" column is met, I wanted the months column to be highlighted based on the start and end date.

Fruitstorestart dateend dateSep-21Oct-21Nov-21Dec-21Jan-22Feb-22
Applewhole10/1/202111/2/2021greengreengreen
orangeretail12/15/20211/15/2022redred

Help!

Thanks much,
Jan
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
367
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
With the headers being the beginning of each month:

Book4
ABCDEFGHIJ
1Fruitstorestart dateend dateSep 2021Oct 2021Nov 2021Dec 2021Jan 2022Feb 2022
2Applewhole10/1/202111/2/2021greengreen
3orangeretail12/15/20211/15/2022redred
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:J3Expression=(EOMONTH(E$1,0)>=$C2)*($B2="retail")*(E$1<=$D2)textYES
E2:J3Expression=(EOMONTH(E$1,0)>=$C2)*($B2="whole")*(E$1<=$D2)textNO
 

lewjan

New Member
Joined
Dec 4, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
Thanks @J
With the headers being the beginning of each month:

Book4
ABCDEFGHIJ
1Fruitstorestart dateend dateSep 2021Oct 2021Nov 2021Dec 2021Jan 2022Feb 2022
2Applewhole10/1/202111/2/2021greengreen
3orangeretail12/15/20211/15/2022redred
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:J3Expression=(EOMONTH(E$1,0)>=$C2)*($B2="retail")*(E$1<=$D2)textYES
E2:J3Expression=(EOMONTH(E$1,0)>=$C2)*($B2="whole")*(E$1<=$D2)textNO
Thanks Much JGordon11! When I entered the expression, I also wanted it to highlight months between start and end dates. When I have dates from oct to dec 2021, it only highlighted Oct and Dec.
How can I highlight the months between the start and end dates? Sorry i wasn't clear in my ask. Thanks again!
 
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,127
Messages
5,768,288
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