macro help

[project manager15]

Hi,

I have writen a macro and it does what i want it to do but only for row '5' and i need it to go down to row 27.

It means that you can only edit e5 to k5 once O/C/A is pick from the drop down. ie i want people to fill in B5.

There are other cells inbetween but they cant be changed and are not relevent.

any help would be awesome.

can i attach?

mark


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("e5:k27")) Is Nothing And Target.Count = 1 Then

Select Case Range("b5").Value
Case "O"
Range("E5:K5").Font.ColorIndex = 1

Case "C"
Range("E5:K5").Font.ColorIndex = 1

Case "A"
Range("E5:K5").Font.ColorIndex = 1

Case Else
Range("E5:K5") = 0
Range("E5:K5").Font.ColorIndex = 2
Value = 0
MsgBox "PLEASE COMPLETE O/C/A"

End Select
End If


End Sub

 

[VoG]

Hello and welcome to MrExcel.

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E5:K27")) Is Nothing And Target.Count = 1 Then
    Select Case Range("B5").Value
        Case "O"
            Range("E5:K27").Font.ColorIndex = 1
        Case "C"
            Range("E5:K27").Font.ColorIndex = 1
        Case "A"
            Range("E5:K27").Font.ColorIndex = 1
        Case Else
            Application.EnableEvents = False
            Range("E5:K27").Value = 0
            Range("E5:K27").Font.ColorIndex = 2
            MsgBox "PLEASE COMPLETE O/C/A"
            Application.EnableEvents = True
    End Select
End If
End Sub

 

[project manager15]

cheers for the quick reply, but it wont let me enter data in, it just say please enter o/c/a... i need each row (columnB) to be the control of whether data can be entered in each row ie b7 for e7:k7.

 

[project manager15]

also when i paste data in the event change doesnt kick in, can this be solved?

 

[VoG]

Are those cells protected?

 

[project manager15]

no

 

[VoG]

Perhaps you meant

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
    i = Target.Row
    Select Case Range("B5").Value
        Case "O"
            Range("E" & i & ":K" & i).Font.ColorIndex = 1
        Case "C"
            Range("E" & i & ":K" & i).Font.ColorIndex = 1
        Case "A"
            Range("E" & i & ":K" & i).Font.ColorIndex = 1
        Case Else
            Application.EnableEvents = False
            RRange("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

 

[project manager15]

no sorry it still wont let me input data in the range.

 

[VoG]

no sorry it still wont let me input data in the range.

Then surely it must be protected

 

[project manager15]

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...