Clear Cells and Worksheet_Change Q

[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

 

[Nigel]

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?

 

[Bill Renaud]

This code in your ClearCells routine:

Range("D7").Select
Selection.Copy
Range("D7:J30").Select
Selection.PasteSpecial _
Paste:=xlAll, _
Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False

1. You are changing more than one cell at a time, so Target is a
multiple-cell range. This is what is producing the Type Mismatch error in
the Worksheet_Change event handler. Change your ClearCells routine to set
each cell one at the time.

2. In your Worksheet_Change event handler, you are lower-casing the value
of the cell in your Select Case statement, then comparing it to a string
that has upper-case letters in it (i.e. "Country 1").

You should probably also have a "Case Else" clause after the 3 cases you
have listed, in case the value of the Target cell changes back to some
other value that needs the cell colors changed back to the original.

Case Else
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone