S
Sean
I have the following code that clear cell content with a Range
Sub ClearCells()
Application.ScreenUpdating = False
Sheets("Log").Activate
ActiveSheet.Unprotect Password:="123"
Range("D7").Select
Selection.ClearContents
Application.DisplayFormulaBar = False
Range("D7").Select
Selection.Copy
Range("D7:J30").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
Range("D7").Select
ActiveSheet.Protect Password:="123", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub
I also have a Worksheet_Change code that, basically changes a cells
colour when data is selected.
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "D7:J30" '<==== change to suit
Application.EnableEvents = True
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "Country 1":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red
Case "Country 2":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red
Case "Country 3":
.Font.ColorIndex = 2
.Interior.ColorIndex = 5 'blue
End Select
End With
End If
End Sub
My issue is that when I run my ClearContents macro (it clears the
cells) but I hit debug within the Worksheet_Change code at line (Type
Mismatch):-
Select Case LCase(.Value)
Why is that and how can I fix?
Thanks
Sub ClearCells()
Application.ScreenUpdating = False
Sheets("Log").Activate
ActiveSheet.Unprotect Password:="123"
Range("D7").Select
Selection.ClearContents
Application.DisplayFormulaBar = False
Range("D7").Select
Selection.Copy
Range("D7:J30").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
Range("D7").Select
ActiveSheet.Protect Password:="123", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub
I also have a Worksheet_Change code that, basically changes a cells
colour when data is selected.
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "D7:J30" '<==== change to suit
Application.EnableEvents = True
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "Country 1":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red
Case "Country 2":
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red
Case "Country 3":
.Font.ColorIndex = 2
.Interior.ColorIndex = 5 'blue
End Select
End With
End If
End Sub
My issue is that when I run my ClearContents macro (it clears the
cells) but I hit debug within the Worksheet_Change code at line (Type
Mismatch):-
Select Case LCase(.Value)
Why is that and how can I fix?
Thanks