color cells in an table between to dates

L

Legacy 241886

Guest
Hello,

I am new in this forum. My first question to my project is, if it is possible and if yes if someone can give me any advice how to do it.

I have a table in worksheet 1 with one column for the starting date and the next column for the ending date. In the next worksheet (worksheet2) I have a table in with every column is a new day f.e. cell A1 = 04/01/13 cell B1 = 04/02/13 C1= 04/03/13 and so on. I would like to write a macro which colors all the cells between the starting date and the ending date out of worksheet 1. So excel needs to realize the starting and ending date of worksheet one and find these dates in worksheet 2 and color every cell between these dates.

Do you think this is possible?

Thank you very much!

Dänny
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello,

There is at least one way to accomplish this without using a macro. If you are open to other options, conditonal formatting can be used. However, it cannot be used to reference another worksheet, but if you are able to use cells in worksheet 2 to reference worksheet 1 it will be OK.

Assuming that in worksheet 1, A2 has the start date and B2 has the end date, in worksheet 2 you cen enter in cell D12 (for example) =Sheet1!A2 and E12 =Sheet1!B2.

With all your dates in the first row of worksheet 2, highlight the entire row, choose Format, Conditional Formatting, Cell Value is between =$D$12 and =$E$12, format the cells to the colour of your choice.

Once this is done, any change made in the dates in worksheet 1 will automatacally highlight the correct dates in worksheet 2.

I hope this helps.
 
L

Legacy 241886

Guest
Hello miss_ell

Thank you for your answer. I tried out your advice with Conditional Formatting. But my Problem is: Then in every cell has be an date. But it should be a calender which shows you, when something is borrowed to who. So in the first row will be the dates and in the first colums the borrowed things. And in this table should be the horizontal bars with the information from which date to which date the things are out.

Do you think, this is possible with Conditional Formatting too?

Thanks a lot!

Dänny
 

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello Danny,

Sorry for the late reply.

It can definitely be done with conditional formatting.

Column A = item list, listed from A2 downwards
Column B = start date
Column C = end date
Dates from D1, copied across

In a small example I've got items 1 to 9 and dates from 01/04/2013 to 15/04/2013.

Select the range from D2:M10, choose Format, Conditional Formatting, change the dropdown to Formula Is, then paste the following formula:
=AND(D$1 > =$B2,D$1 < =$C2), then format red.

Result table:

Start
End
01/04/2013
02/04/2013
03/04/2013
04/04/2013
05/04/2013
06/04/2013
07/04/2013
08/04/2013
09/04/2013
10/04/2013
Item1
30/03/2013
02/04/2013
xx
Item2
01/04/2013
01/04/2013
x
Item3
10/04/2013
12/04/2013
x
Item4
05/04/2013
09/04/2013
xxxxx
Item5
05/04/2013
06/04/2013
xx
Item6
03/04/2013
05/04/2013
xxx
Item7
04/04/2013
09/04/2013
xxxxxxx
Item8
09/04/2013
10/04/2013
xx
Item9
01/04/2013
06/04/2013
xxxxx

<TBODY>
</TBODY>

I hope it works for you. Please let me know if you have any problems.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

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