Script code

[Frick]

I would like to generate a script code that will in effect create a gnatt
chart.

In F4:GZ4 I have dates which equal the start of each month across the col's
(eg. 1/1/04, 2/1/04 etc)
In F5:GZ5 I have dates which equal the end of each month across the col's
(eg. 1/31/04, 2/28/04 etc)

In cols B&D rows 10 through 800 I enter dates, b=start dates, d=end dates.

What I need is a script that when the dates are entered in any given row (10
through 800) that it matches the start and end dates with the dates in rows
4 and 5 and returns a "======" those col's that fall within the start and
end dates.

I have a index formula that does this but I need to send this sheet out to a
couple of people and while they can enter the dates they continually mess
with the formula which then ruins the gnatt view. That is why I hope that
there is a script that can be written and entered for the range F10:GZ800
that would automatically handle this.

Thanks for all help

 

[Frank Kabel]

Hi
instead of using a macro why not protect your formulas / the sheet:
- first select all cells for which you want to allow entries
- goto 'Format - Cells - Protection' and uncheck 'Locked'
- After this goto 'Tools - Protection' and protect your worksheet

 

[Frick]

Franks,

thanks for the reply. The problem with the protection lock is that after
entering much of the base information, there comes a time when somone has to
insert rows between two or more items so that the order is maintained and
that is when the problems arise. Using locked cells prevents adding or
maintaing the formula.

Again, thanks for the fast response.

 

[Mike Lewis]

If you are willing to live without the "=====" text, you
can use conditional formatting, where the cell will turn
red if the date at the top of the column is between the
start and end dates. The formula to enter in the
conditional formatting will be something like:

=AND(H$2<$D4,H$2>$C4)

where H2 is the column header date, and D4 and C4 are
start and end dates. Copy this text to the gantt chart
area and then protect your sheet except for start and end
dates

Good Luck

Mike