Hi
A few things

You can clear the range by using - Range("D7:J30").ClearContents
There is no need to clear one cell and then copy to all others.

The Target returned is a range, therefore your Case test cannot be applied,
and has to carried out for each value in the range. Something like

For Each tcell In Target

With tcell

Select Case LCase(.Value)
Case Is = "country 1"
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red

Case Is = "country 2"
.Font.ColorIndex = 2
.Interior.ColorIndex = 3 'red

Case Is = "country 3"
.Font.ColorIndex = 2
.Interior.ColorIndex = 5 'blue

End Select
End With

Next tcell

The logic to test using LCase values, then compare to an upper case value
(Lcase(.value) = "Country 1") will never work, should all be lower case in
the right hand side of the test.

Also in the Case test, using the single line operator (:) will not work as
you have multiple lines, change the case testing to - Case Is = "country
1" etc. as shown in above example.

Also your logic to test the intersect may not be what you wanted?


--

Regards,
Nigel




"Sean" wrote in message
ups.com...
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