Hi, I have been trying to update the color of corresponding tabs when the table column containing the status is changed. I have written the following code to achieve the same:
Before the validations can be performed, however, I am getting an error that says: Compile Error - Argument not optional - the code stops at the method declaration line whenever I change the status of a cell in the D column (which contains the STATUS). Please try to point out what I am doing incorrectly here, thanks.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("D:D")) Is Nothing Or Target.Cells.Count > 1 Then
Dim mystat As String
Dim mycell As String
Application.EnableEvents = False
mystat = Target.Value
Target.Select
mycell = "INC" & Range("B" & ActiveCell.Row).Value
If WorksheetExists(mycell) Then
If mystat = "In Progress" Then
Worksheets(mycell).Tab.Color = vbYellow
End If
With Worksheets(mycell).Tab
Select Case mystat
Case "In Progress"
.Color = RGB()
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Before the validations can be performed, however, I am getting an error that says: Compile Error - Argument not optional - the code stops at the method declaration line whenever I change the status of a cell in the D column (which contains the STATUS). Please try to point out what I am doing incorrectly here, thanks.