VBA Code - Repeating block through all rows in worksheets

  • Hi, I'm very new at this so please bare with me; not sure if this code is the most efficient one for my objective. So the objective is to Make unactive a cell depending on the value on a previous cell, then make another cell unactive depending on this last cell. Basically if I have 5 cells, I want them to unactive cells 2-5 and in order for activate cell #5 you will have to first activate 2 and so on and so on, like a chain in reaction. The problem is that I need this to work in all rows, not only 6 as shown below. Please help.


    What I have is this:


    Private Sub Worksheet_Change(ByVal Target As Range)

    If ((Target.Address = "$C$6") And (Target.Value = "DONE")) Then Range("H6").Interior.ColorIndex = xlNone

    If ((Target.Address = "$C$6") And (Target.Value <> "DONE")) Then

    OldValue = Range("H6")

    With Range("H6").Interior

    .ColorIndex = 15

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End If


    If ((Target.Address = "$H$6") And (Range("C6") <> "DONE")) Then

    Application.EnableEvents = False

    Target.Value = OldValue

    Application.EnableEvents = True

    End If


    If ((Target.Address = "$H$6") And (Target.Value = "DONE")) Then Range("M6").Interior.ColorIndex = xlNone

    If ((Target.Address = "$H$6") And (Target.Value <> "DONE")) Then

    OldValue = Range("M6")

    With Range("M6").Interior

    .ColorIndex = 15

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End If


    If ((Target.Address = "$M$6") And (Range("H6") <> "DONE")) Then

    Application.EnableEvents = False

    Target.Value = OldValue

    Application.EnableEvents = True

    End If


    If ((Target.Address = "$M$6") And (Target.Value = "DONE")) Then Range("R6").Interior.ColorIndex = xlNone

    If ((Target.Address = "$M$6") And (Target.Value <> "DONE")) Then

    OldValue = Range("R6")

    With Range("R6").Interior

    .ColorIndex = 15

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End If

    End Sub

  • Not 100% sure if I've got the right end of the stick but give this a go on a copy of your workbook just incase it's totally off base.