# For Each Cell in Selection



## Housewins2 (Feb 22, 2009)

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 (May 20, 2008)

The following example may be helpful. Consider the following 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 (Feb 22, 2009)

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


----------

