Dear all,
i have two different groups of coding, both of which work perfectly by themselves however i am unable to get them to both work side by side.
The first set was written by someone who knew what they were doing, the second set was scrabbled together with what help i could find from Google (so it probably isnt the best way it could be done)
With your infinitely greater knowledge of VBA, can anybody assist me with combining the following two sets of code correctly?
Please be gentle
First set:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
Select Case Target.Column
Case 11 ' "K"
Select Case Target.Row
Case 2 To 150
If Target.Value = "Engineer to Review" Then
If IsEmpty(Target.Offset(0, 5)) Then
Application.EnableEvents = False
Target.Offset(0, 5).Value = Now()
Application.EnableEvents = True
End If
ElseIf Target.Value = "Uploaded to Server & Alarm" Then
If IsEmpty(Target.Offset(0, 6)) Then
Application.EnableEvents = False
Target.Offset(0, 6).Value = Now()
Application.EnableEvents = True
End If
End If
End Select
Case 16 ' "P"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -5).Value = "Engineer to Review"
Application.EnableEvents = True
End If
End Select
Case 17 ' "Q"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -6).Value = "Uploaded to Server & Alarm"
Application.EnableEvents = True
End If
End Select
Case Else
Exit Sub
End Select
End Sub
-------------------------------------------------------
Second set:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Protect AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True, AllowInsertingRows:=True
Select Case Target.Value
Case "" 'IF BLANK'
Target.Interior.ColorIndex = xlNone 'BLANK'
Case "ENTER EXAM STATUS" 'LIGHT GREY'
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1
Case "Cancelled - See Comments" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 1
Case "Possession Req'd - See Comments" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 1
Case "Report held by Author" 'LIGHT YELLOW'
Target.Interior.ColorIndex = 36
Target.Font.ColorIndex = 1
Case "Report held by Engineer" 'LIGHT GREEN'
Target.Interior.ColorIndex = 35
Target.Font.ColorIndex = 1
Case "Notes on Server - Unassigned" 'TAN'
Target.Interior.ColorIndex = 40
Target.Font.ColorIndex = 1
Case "Engineer to Review" 'LAVENDER'
Target.Interior.ColorIndex = 39
Case "Uploaded to Server & Alarm" 'LIME'
Target.Interior.ColorIndex = 43
Target.Font.ColorIndex = 1
Case "Unknown" 'Red text'
Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 3
Case "N" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45
Case "Y" 'GREEN'
Target.Interior.ColorIndex = 10
Target.Font.ColorIndex = 36
Case "OVERDUE" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 36
Case Else
End Select
End Sub
Bookmarks