Search code examples
excelvba

Excel Macro Compile Error - Argument not Optional

I am getting an error message when I run my vba code in excel. The error message is a compile error with a message "Argument not Optional". I do not know where I am going wrong in the code.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

If Not Intersect(Target, Union(Range("K15:K31"))) Is Nothing Then
If Target.Interior.ColorIndex = 43 Then
Target.Interior.ColorIndex = 0
ActiveCell.Value = ""
Else: Target.Interior.ColorIndex = 43
ActiveCell.Value = ChrW(&H2713)
ActiveCell.Font.ColorIndex = 2
End If
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Set Cancel = True

If Not Intersect(Target, Union(Range("K15:K31"))) Is Nothing Then
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 48
ActiveCell.Value = "N/A"
ElseIf Target.Interior.ColorIndex = 48 Then
Target.Interior.ColorIndex = 0
Else: Target.Interior.ColorIndex = 3
ActiveCell.Value = ChrW(&H2716)
ActiveCell.Font.ColorIndex = 2
End If
End If
End Sub
Solution

  • The Union method requires two arguments. In your code to use this method is unneccessary. Simply delete it and use only the Range object.

    If Not Intersect(Target, Range("K15:K31")) Is Nothing Then