Dates related cells

[matthew.newsome]

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

 

[Guest]

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.

 

[Alan]

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

 

[Alan]

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

Regards,

Alan

 

[matthew.newsome]

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

Regards,

Alan







- Show quoted text -

Thanks guys I'll try them both