Hi all,
Thanks to Craig Ottley and Batman in the Ozgrid Forum I have been able to set up functions in Worksheet_Change(). My problem is that each function will work correctly on their own, but when I try to put 2 functions together, I can't get them to work independently. Is this possible?
' First function
Dim wSht As Worksheet
Set wSht = Worksheets("Rosters")
MyFix
On Error GoTo errHandler
If WorksheetFunction.Count(wSht.Range("Multitask_atts_DWSs"), Target) > 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = wSht.Range("Y266") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, wSht.Range("DWS_List"), 0), 0)
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If
' Second function for warning cells
If Cells(168, 23).Value = "Quota >Req'd" Then
Warning.Show
ActiveCell.Offset(0, 0).Range("A1").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Else
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End If
Each seems to be aimed at individual cell/sets of cells changes, but they are getting mixed up.
Any pointers would be greatly appreciated.
Thanks
Ginny
http://www.ozgrid.com/forum/member.php?u=35962&
Thanks to Craig Ottley and Batman in the Ozgrid Forum I have been able to set up functions in Worksheet_Change(). My problem is that each function will work correctly on their own, but when I try to put 2 functions together, I can't get them to work independently. Is this possible?
' First function
Dim wSht As Worksheet
Set wSht = Worksheets("Rosters")
MyFix
On Error GoTo errHandler
If WorksheetFunction.Count(wSht.Range("Multitask_atts_DWSs"), Target) > 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = wSht.Range("Y266") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, wSht.Range("DWS_List"), 0), 0)
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If
' Second function for warning cells
If Cells(168, 23).Value = "Quota >Req'd" Then
Warning.Show
ActiveCell.Offset(0, 0).Range("A1").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Else
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End If
Each seems to be aimed at individual cell/sets of cells changes, but they are getting mixed up.
Any pointers would be greatly appreciated.
Thanks
Ginny
http://www.ozgrid.com/forum/member.php?u=35962&