Worksheet Change

[Jammydan]

hi Can anybody help with the below code. It works fine until I add the "For each cell in cash" lines?

Many thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range '** only look at single cell changes
    Dim ltv As Range
    Dim cash As Range
    Set ltv = Range("AC8:AC20")
    Set cash = Range("L8:L20")
ActiveSheet.Unprotect
    For Each cell In cash
        If cell.Value = "Cash" Then
        cell.Offset(0, 10) = cell.Offset(0, 6)
        ElseIf cell.Value = "Shares" Then
        cell.Offset(0, 10) = cell.Offset(0, 9)
    End If
Next
    For Each cell In ltv
        If cell.Value < cell.Offset(0, -2) And cell.Offset(0, -26) = "TOTAL" Then ' Orange
        Range("C" + CStr(cell.Row) + ":AC" + CStr(cell.Row)).Interior.ColorIndex = 46
        ElseIf cell.Value < cell.Offset(0, -1) And cell.Offset(0, -26) = "TOTAL" Then ' Dark Orange
        Range("C" + CStr(cell.Row) + ":AC" + CStr(cell.Row)).Interior.ColorIndex = 45
        ElseIf cell.Value > cell.Offset(0, -2) And cell.Offset(0, -26) = "TOTAL" Then ' Green
        Range("C" + CStr(cell.Row) + ":AC" + CStr(cell.Row)).Interior.ColorIndex = 43
    End If
Next
ActiveSheet.Protect
    If Target.count > 1 Then Exit Sub
    Set rng = Range("W8:W200")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Date
    If Intersect(Target, rng) = "" Then
    Target.Offset(0, 1) = ""
End If
ActiveSheet.Protect
End Sub

 

[njimack]

Dim cell as Range

 

[Gary McMaster]

You should start your procedure with:

Application.EnableEvents = False

and end it with:

Application.EnableEvents = True

Each time you "CHANGE" something inside the worksheet "CHANGE" event you cause the event/procedure to call itself recursively.

Gary

 

[Jammydan]

Thanks guys, its not really working as I need the cell to update when the sheet is activated too.

 

[njimack]

Thanks guys, its not really working as I need the cell to update when the sheet is activated too.

That would be a Worksheet_Activate event. Suggest you create 2 events - Worksheet_Activate and Worksheet_Change, both of which call the same procedure, held in a different module.