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


" 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
wrote in message
oups.com...
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" wrote in message
...
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
wrote in message
oups.com...
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" wrote:
Sorry, "it will highlight column B and B" should read "B and C".

Regards,

Alan

"Alan" wrote in message

...



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
wrote in message
roups.com...
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