shade range of cells based on dates

J

johnandbeth

Board Regular
Joined
Apr 8, 2002
Messages
168
I want to shade cells in a section I have as a calendar based on dates in a column.

Row 1 E1:NE1 = each day in the year (vertically)

Column 1 = begin date
Column 2 = end date

If Cell C5 shows 1/14 and Cell D5 shows 2/28, I want the corresponding cells in row 5 under the dates from 1/14 through 2/28 to fill-in with shading, in this case Q5:BK5.

I will add many rows below it with other begin/end dates and will want their corresponding cells to be shaded.

Any suggestions?

Thanks,
~Beth
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Use conditional formatting where you set up a rule to shade the field based upon your values.
 
Upvote 0
Try Conditional Formatting with a formula
=AND(E1>=$C$1,E1<=$D$1)
 
Upvote 0
I tried the conditional formula "=AND(E1>=$C$1,E1<=$D$1)" and it didn't work. Any other suggestions?

Thanks!
~Beth
 
Upvote 0
My table
Name from A2:A9
St_Date B2
Fn_Date C2
From D1:NE1 dates
I wanted was to plot/shade the dates between start and End data.

Formula i used from D2:NE9, =IF(D1<$B$2,"",IF(D1<$C$2,$A$2,""))

conditional formatting:
Format Cell with specific text containing 1st from my names list
applied formatting as per my req and did same for rest ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro to affect cell background color
Replies
3
Views
201
mumps
mumps
M
  • Question
VBA Code to copy rows by date from one tab to another / repeat based on new date
Replies
3
Views
210
kevin9999
K
S
  • Question
How to shade a cell within a column if the date is in the next 6 months or less?
Replies
2
Views
493
Kerryx
K
A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
104
anuradhagrewal
A
G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
354
MARK858
MARK858

Forum statistics

Threads
1,214,561
Messages
6,120,242
Members
448,951
Latest member
jennlynn

Share this page

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