Hi Howard,

Am Fri, 12 Jul 2013 04:36:23 -0700 (PDT) schrieb Howard:

The target.cells.count allows more than 1 cell selected in range, but takes the input in the activecell of the multiple cell selection.

The ElseIf "X_Clear" works fine from a separate sub, but it won't respond in the change event macro.

try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$A$1:$H$10")) Is Nothing Or _
Target.Count 1 Then Exit Sub

Dim c As Range
Dim Data1 As Range
Dim Ans As Integer
Dim mycolor As Integer

Set Data1 = Range("A1:H10")

For Each c In Data1
Select Case c.Value
Case "X_Clear"
Ans = MsgBox(" " _
& " Clear Data1 ?", vbYesNo)
Case 0
mycolor = xlNone
Case Is = 5
mycolor = 5
Case Is <= 4
mycolor = 3
End Select
If Ans = vbYes Then
Data1.ClearContents
Data1.Interior.ColorIndex = xlNone
End If
c.Interior.ColorIndex = mycolor
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2