# 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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.

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

Hello Danny,

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.

Last edited:

Replies
2
Views
252
Replies
4
Views
170
Replies
7
Views
254
Replies
3
Views
269
Replies
3
Views
633

1,219,819
Messages
6,150,403
Members
450,960
Latest member
GB2

### 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.

### Which adblocker are you using?

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

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