color cells in an table between to dates

[Legacy 241886]

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

 

[miss_ell]

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.

 

[Legacy 241886]

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]

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

x

x

Item2

01/04/2013

01/04/2013

x

Item3

10/04/2013

12/04/2013

x

Item4

05/04/2013

09/04/2013

x

x

x

x

x

Item5

05/04/2013

06/04/2013

x

x

Item6

03/04/2013

05/04/2013

x

x

x

Item7

04/04/2013

09/04/2013

x

x

x

x

x

x

x

Item8

09/04/2013

10/04/2013

x

x

Item9

01/04/2013

06/04/2013

x

x

x

x

x

<TBODY>
</TBODY>

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