hi,

having problems getting my macro to work.

i want it so that you can only edit rows in e5 to k27 once O/C/A is picked
from the drop down in b5 to b27. ie i want people to fill in B5, then the
rest of the row.

if it helps set the scene each row is a task/project, and column b is a type
of work, e to k is sat - fri where hours are entered...

i also have a problem with when data is pasted in it doesnt trigger the
event change.

any help would be awesome,

cheers

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Not Intersect(Target, Range("E5:K27")) Is Nothing And Target.Count = 1 Then
'it is one of E5:K27

i = Target.Row
Select Case Range("B5" & i).Value
Case "O"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select

Case "C"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select
Case "A"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select

Case Else
Application.EnableEvents = False
Range("E" & i & ":K" & i).Value = 0
Range("E" & i & ":K" & i).Font.ColorIndex = 2
MsgBox "PLEASE COMPLETE O/C/A"
Application.EnableEvents = True
End Select
End If
End Sub