For Each Cell in Selection

Housewins2 · 03-05-2009, 01:58 AM

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("ar1:ar30000")) Is Nothing Then

'
With Target(1, 10)
.Value = Date

End With

End If

End Sub

This code above does ALMOST exactly what I want it to do. The problem arises when I select multiple cells at once. Then it only looks at the first cell in the selection. Below is my attempt to tell the macro to do it for every cell in the selection and put the date in the field ten cells to the right.

For example.

if cell A1 = Dog and I highlight A1 and change that cell, cell K1 should say the date

if cell A1 = dog and cell A2 equals Cat and I select both cell A1 and A2, and then hit the delete button, I want cell K1 to say the date and at the same time change cell L1 to also say the date.

Below is my poor attempt to make this work. Please Help.

Private Sub Worksheet_Change(ByVal CurCell As Range)

If Target.Cells.Count > 100 Then Exit Sub

For Each CurCell In Selection

If Not Intersect(CurCell, Range("q5:at1000")) Is Nothing Then

With CurCell(1001, 1)

.Value = Date

Next CurCell

End With

End If

End Sub

I want to be able to run this code for multimple cells that are selected at once. I cant get the code to work. Please Help

David M58 · 03-05-2009, 05:11 PM

The following example may be helpful. Consider the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurCell As Range
    If Target.Cells.Count > 100 Then
        Exit Sub
    End If
    Application.EnableEvents = False
    For Each CurCell In Target
        If Not Intersect(CurCell, Range("A1:B1000")) Is Nothing Then
           CurCell.Offset(0, 9).Value = Date
        End If
    Next CurCell
    Application.EnableEvents = True
End Sub

If the code above is active and you select the range A1:A2 and hit the Delete key, the above code will go through each cell in the selected range (A1:A2) and place the date in the cell located nine cells to the right. In this example, the date will appear in J1:J2. As written above, the code will not add the date if you selected more than 100 cells, nor will it add the date for any selected cell outside of the range A1:B1000.

If you wish to have the range where the dates appear transposed, so that if, for example, the selection is in one column and multiple rows (A1:A2) but the dates are in one row and multiple columns (J1:K1), it will be necessary to make modifications based on the line

CurCell.Offset(0, 9).Value = Date

Housewins2 · 03-05-2009, 07:38 PM

Thanks a lot. You could not have been of more help. Greatly appreciated.

03-05-2009, 01:58 AM	#1 (permalink)
Housewins2 Registered User Join Date: Feb 2009 Posts: 4 OS: vista	For Each Cell in Selection Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("ar1:ar30000")) Is Nothing Then ' With Target(1, 10) .Value = Date End With End If End Sub This code above does ALMOST exactly what I want it to do. The problem arises when I select multiple cells at once. Then it only looks at the first cell in the selection. Below is my attempt to tell the macro to do it for every cell in the selection and put the date in the field ten cells to the right. For example. if cell A1 = Dog and I highlight A1 and change that cell, cell K1 should say the date if cell A1 = dog and cell A2 equals Cat and I select both cell A1 and A2, and then hit the delete button, I want cell K1 to say the date and at the same time change cell L1 to also say the date. Below is my poor attempt to make this work. Please Help. Private Sub Worksheet_Change(ByVal CurCell As Range) If Target.Cells.Count > 100 Then Exit Sub For Each CurCell In Selection If Not Intersect(CurCell, Range("q5:at1000")) Is Nothing Then With CurCell(1001, 1) .Value = Date Next CurCell End With End If End Sub I want to be able to run this code for multimple cells that are selected at once. I cant get the code to work. Please Help

03-05-2009, 05:11 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: For Each Cell in Selection The following example may be helpful. Consider the following code: Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim CurCell As Range If Target.Cells.Count > 100 Then Exit Sub End If Application.EnableEvents = False For Each CurCell In Target If Not Intersect(CurCell, Range("A1:B1000")) Is Nothing Then CurCell.Offset(0, 9).Value = Date End If Next CurCell Application.EnableEvents = True End Sub If the code above is active and you select the range A1:A2 and hit the Delete key, the above code will go through each cell in the selected range (A1:A2) and place the date in the cell located nine cells to the right. In this example, the date will appear in J1:J2. As written above, the code will not add the date if you selected more than 100 cells, nor will it add the date for any selected cell outside of the range A1:B1000. If you wish to have the range where the dates appear transposed, so that if, for example, the selection is in one column and multiple rows (A1:A2) but the dates are in one row and multiple columns (J1:K1), it will be necessary to make modifications based on the line CurCell.Offset(0, 9).Value = Date Last edited by David M58; 03-05-2009 at 05:12 PM.

03-05-2009, 07:38 PM	#3 (permalink)
Housewins2 Registered User Join Date: Feb 2009 Posts: 4 OS: vista	Re: For Each Cell in Selection Thanks a lot. You could not have been of more help. Greatly appreciated.