"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work

[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.

Thanks.
Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

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

Dim c As Range
Dim Data1 As Range
Set Data1 = Range("A1:H10")

For Each c In Range("Data1")
If c.Value = 0 Then
c.Interior.ColorIndex = xlNone
ElseIf c.Value >= 5 Then
c.Interior.ColorIndex = 4
ElseIf c.Value <= 4 Then
c.Interior.ColorIndex = 3

ElseIf c.Value = "X_Clear" Then
Dim Ans As Variant
Ans = MsgBox(" " _
& " Clear Data1 ?", vbYesNo)
Select Case Ans
Case vbYes
Range("Data1").ClearContents
Range("Data1").Interior.ColorIndex = xlNone
Case vbNo
Exit Sub
End Select
End If

Next
End Sub

 

[Claus Busch]

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.

 

[Howard]

Bravo, Claus, works fine.

Am I misunderstanding the line below about Target.Count?
I can still select more than one cell in the target range and the activecell of that selection will take an input. I guess its really is no big deal, just thought that would eliminate multiple selections.

Or Target.Count > 1 Then Exit Sub

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Fri, 12 Jul 2013 06:02:36 -0700 (PDT) schrieb Howard:

Am I misunderstanding the line below about Target.Count?
I can still select more than one cell in the target range and the activecell of that selection will take an input. I guess its really is no big deal, just thought that would eliminate multiple selections.

yes, but you only change the active cell and that is only one cell. But
you will get an error message when you select more cells and try to
delete them. Or if you select more than one cell and make an input in
the active cell and then press CTRL+Shift+Enter all cells will be filled
but the makro doesn't start because target.count >1


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Fri, 12 Jul 2013 06:02:36 -0700 (PDT) schrieb Howard:







yes, but you only change the active cell and that is only one cell. But

you will get an error message when you select more cells and try to

delete them. Or if you select more than one cell and make an input in

the active cell and then press CTRL+Shift+Enter all cells will be filled

but the makro doesn't start because target.count >1





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Okay, got it.

Thanks again.

Regards,
Howard