Making an Automated Grid Worksheet

  • Thread starter Thread starter Ange Kappas
  • Start date Start date
A

Ange Kappas

Hi,
I would like to set up a worksheet which takes values from another
worksheet(data) and colors in each corresponding cell according to the range
set in the data worksheet. Let me be more specific.

We have a worksheet which on the top row wise has the dates corresponding to
each day.
For Example: 1/5/2004, 2/5/2004, 3/5/2004, 4/5/2004, 5/5/2004,
6/5/2004, e.t.c.

And Column wise we have the room numbers.
For Example:

101
102
103
104
105
e.t.c.

Now on the separate worksheet (data) I have values like below:

Room Number Name Arrival Date Departure Date

101 Jones 2/5/2004 9/5/2004
103 Smith 3/5/2004
10/5/2004
105 Dees 7/5/2004 9/5/2004
105 Clark 10/5/2004 12/5/2004
e.t.c.

Now on open of Excel I would like it either using a Macro or Visual Basic to
take the values from the Data Worksheet and color in any particular color
all the corresponding cells say for example "Jones" in "Yellow" all the
cells from the row "101" from the dates 2/5/2004 till the 9/5/2004.

If anyone knows how I can do this it would be greatly appreciated...

Bye..
 
Now on open of Excel I would like it either using a Macro or Visual
Basic to take the values from the Data Worksheet and color in any
particular color all the corresponding cells say for example "Jones"
in "Yellow" all the cells from the row "101" from the dates 2/5/2004
till the 9/5/2004.

If anyone knows how I can do this it would be greatly appreciated...
Click to expand...

It can be done solely with worksheet functions and conditional formatting
if you are willing to have two additional columns in the data worksheet
that are combinations of the room number and the date. (I'm going to use
roomnumber*100000+date, but roomnumber*100+month(date) would probably
work also).

Enter this formula into the cells you want to color, making the necessary
changes. I've assumed that the data is in worksheet Data, cells A1:D4,
and the additional columns are in worksheet Data, cells E1:F4. And that
the table of cells you want to color has row headings in column A and
column headings in row 1 (the actual cells start in B2).
Put this in B2 and fill down and right.
=IF(MATCH(100000*$A2+B$1,Data!$E$1:$E$4,1)=IF(ISNA(MATCH(100000*$A2+B$1-
1,Data!$F$1:$F$4,1)),1,MATCH(100000*$A2+B$1-1,Data!$F$1:$F$4,1)+1),INDEX
(Data!$B$1:$B$4,MATCH(100000*$A2+B$1,Data!$E$1:$E$4,1),1),"")

Then the cells will contain the name of the person who registered the
room.

You can then use conditional formatting to color the cell as you like.
 
Back
Top