Can anyone steer me on the right track, I have a spreadsheet with
loads of columns which are weeks worked. I have a start and end date
on the same row for a project, as these dates are completed I manually
highlight the cells (as though it were a ghant plan) is there anyway
you can link the cells filling with a highlight to the actual dates.

I realise this is probably a whole solution but just wanted pointing
in the right direction.

many thanks Matt

you could write a macro to do the coloring. If you mean conditional
formatting, you could use a formula like

=And(F$1>=B9,F$1+6<=C9)

with start date in column B and end date in column C and the date for the
start of the week in F1.

--
regards,
Tom Ogilvy


"(E-Mail Removed)" wrote:

> Can anyone steer me on the right track, I have a spreadsheet with
> loads of columns which are weeks worked. I have a start and end date
> on the same row for a project, as these dates are completed I manually
> highlight the cells (as though it were a ghant plan) is there anyway
> you can link the cells filling with a highlight to the actual dates.
>
> I realise this is probably a whole solution but just wanted pointing
> in the right direction.
>
> many thanks Matt
>
>

You could place this in the sheet module. If a completion date is entered in
column C, it will highlight column B and B. Change Target.Column = 3 to the
count of the completion date column. Change the -1 in Target.Offset(0,-1) to
the Target.Column offset to be highlighted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value > "" Then
Target.Offset(0, -1).Interior.ColorIndex = 6
Target.Interior.ColorIndex = 6
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub



Regards,

Alan
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can anyone steer me on the right track, I have a spreadsheet with
> loads of columns which are weeks worked. I have a start and end date
> on the same row for a project, as these dates are completed I manually
> highlight the cells (as though it were a ghant plan) is there anyway
> you can link the cells filling with a highlight to the actual dates.
>
> I realise this is probably a whole solution but just wanted pointing
> in the right direction.
>
> many thanks Matt
>

Sorry, "it will highlight column B and B" should read "B and C".

Regards,

Alan


"Alan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You could place this in the sheet module. If a completion date is entered
> in column C, it will highlight column B and B. Change Target.Column = 3 to
> the count of the completion date column. Change the -1 in
> Target.Offset(0,-1) to the Target.Column offset to be highlighted.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> On Error GoTo ErrHandler
> Application.EnableEvents = False
> If Target.Column = 3 Then
> If Target.Value > "" Then
> Target.Offset(0, -1).Interior.ColorIndex = 6
> Target.Interior.ColorIndex = 6
> End If
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
>
>
> Regards,
>
> Alan
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Can anyone steer me on the right track, I have a spreadsheet with
>> loads of columns which are weeks worked. I have a start and end date
>> on the same row for a project, as these dates are completed I manually
>> highlight the cells (as though it were a ghant plan) is there anyway
>> you can link the cells filling with a highlight to the actual dates.
>>
>> I realise this is probably a whole solution but just wanted pointing
>> in the right direction.
>>
>> many thanks Matt
>>
>
>

On 1 Mar, 00:22, "Alan" <R...@cinci.rr.com> wrote:
> Sorry, "it will highlight column B and B" should read "B and C".
>
> Regards,
>
> Alan
>
> "Alan" <R...@cinci.rr.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > You could place this in the sheet module. If a completion date is entered
> > in column C, it will highlight column B and B. Change Target.Column = 3 to
> > the count of the completion date column. Change the -1 in
> > Target.Offset(0,-1) to the Target.Column offset to be highlighted.
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > On Error GoTo ErrHandler
> > Application.EnableEvents = False
> > If Target.Column = 3 Then
> > If Target.Value > "" Then
> > Target.Offset(0, -1).Interior.ColorIndex = 6
> > Target.Interior.ColorIndex = 6
> > End If
> > End If
> > ErrHandler:
> > Application.EnableEvents = True
> > End Sub
>
> > Regards,
>
> > Alan
> > <matthew.news...@npower.com> wrote in message
> >news:(E-Mail Removed)...
> >> Can anyone steer me on the right track, I have a spreadsheet with
> >> loads of columns which are weeks worked. I have a start and end date
> >> on the same row for a project, as these dates are completed I manually
> >> highlight the cells (as though it were a ghant plan) is there anyway
> >> you can link the cells filling with a highlight to the actual dates.
>
> >> I realise this is probably a whole solution but just wanted pointing
> >> in the right direction.
>
> >> many thanks Matt- Hide quoted text -
>
> - Show quoted text -

Thanks guys I'll try them both