vba help

[p4nny]

Hello

Hopefully very easy to fix.. I would like this applicable to all cells with C2:C10000 and D2:1000

So if in cell C3 I change the cell to Y, D3 would update..

Works fine at the moment but on a cell C2.

Many thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)   
        If Sheets("Action Plan").Range("c2") = "Y" Then


Sheets("Action Plan").Range("D2") = "Key in the dependency ID"
Else
Sheets("Action Plan").Range("d2") = "N/A"
End If
End Sub

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)   

    If Intersect(Target,Range("C2:C100000)) Is Nothing Then
        Exit Sub
    End If

    Application.EnableEvents = False

    If Target.Value = "Y" Then
        Target.Offset(,1).Value = "Key in the dependency ID"
    Else
        Target.Offset(,1).Value = "N/A"
    End If

    Application.EnableEvents = True

End Sub

 

[CsJHUN]

Try this.

I just want to paste this code with just two small exception. In my version the C100000 would be C10000 :P and did not use the enableevents function.
@Norie: is the enableevents really necessary? What this do here?

 

[Norie]

Since the code in the Change event could change the sheet it could end up triggering itself so, though not entirely necessary, disabling events is probably a good idea.

 

[p4nny]

Thanks guys - code works fine. One question, if I insert a new row into the sheet, I get a "mis-match" error and the code seems to stop working. Is there "wrap around" I can do.. On error.. Resume Next

thanks again

 

[Norie]

You can test how many cells Target consists of, if it's more than one exit.

If Target.Cells.Count > 1 Exit Sub