Refactoring Code - VBA / Macro Script

Contributor

Hi everyone, could someone help me refactor my code, please. I am new to VBA/macro coding. Hence, my coding skills doesn't that good. Wanted to learn to have efficient macro/VBA scripts. Attached were the codes and file on what it is basically doing.  Thanks in advance.

 

 

' Hide / Show Rows
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim xCellsA     As String
    Dim r           As Long
    If Not Intersect(Range("B13"), Target) Is Nothing Then
        Cancel = True
        r = 5 * Target.Row - 51
        xCellsA = r & ":" & r + 6
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsB     As String
    Dim b           As Long
    If Not Intersect(Range("B21"), Target) Is Nothing Then
        Cancel = True
        b = 5 * Target.Row - 83
        xCellsA = b & ":" & b + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsC     As String
    Dim c           As Long
    If Not Intersect(Range("B24"), Target) Is Nothing Then
        Cancel = True
        c = 5 * Target.Row - 95
        xCellsA = c & ":" & c + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsD     As String
    Dim d           As Long
    If Not Intersect(Range("B27"), Target) Is Nothing Then
        Cancel = True
        d = 5 * Target.Row - 107
        xCellsA = d & ":" & d + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsE     As String
    Dim e           As Long
    If Not Intersect(Range("b33"), Target) Is Nothing Then
        Cancel = True
        e = 5 * Target.Row - 131
        xCellsA = e & ":" & e + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsF     As String
    Dim f           As Long
    If Not Intersect(Range("b39"), Target) Is Nothing Then
        Cancel = True
        f = 5 * Target.Row - 155
        xCellsA = f & ":" & f + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsG     As String
    Dim g           As Long
    If Not Intersect(Range("b45"), Target) Is Nothing Then
        Cancel = True
        g = 5 * Target.Row - 179
        xCellsA = g & ":" & g + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsH     As String
    Dim h           As Long
    If Not Intersect(Range("b51"), Target) Is Nothing Then
        Cancel = True
        h = 5 * Target.Row - 203
        xCellsA = h & ":" & h + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsI     As String
    Dim i           As Long
    If Not Intersect(Range("b54"), Target) Is Nothing Then
        Cancel = True
        i = 5 * Target.Row - 215
        xCellsA = i & ":" & i + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsJ     As String
    Dim j           As Long
    If Not Intersect(Range("b60"), Target) Is Nothing Then
        Cancel = True
        j = 5 * Target.Row - 239
        xCellsA = j & ":" & j + 6
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsK     As String
    Dim k           As Long
    If Not Intersect(Range("b68"), Target) Is Nothing Then
        Cancel = True
        k = 5 * Target.Row - 271
        xCellsA = k & ":" & k + 6
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsL     As String
    Dim l           As Long
    If Not Intersect(Range("b76"), Target) Is Nothing Then
        Cancel = True
        l = 5 * Target.Row - 303
        xCellsA = l & ":" & l + 5
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsM     As String
    Dim m           As Long
    If Not Intersect(Range("b83"), Target) Is Nothing Then
        Cancel = True
        m = 5 * Target.Row - 331
        xCellsA = m & ":" & m + 3
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsN     As String
    Dim n           As Long
    If Not Intersect(Range("b88"), Target) Is Nothing Then
        Cancel = True
        n = 5 * Target.Row - 351
        xCellsA = n & ":" & n + 7
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsO     As String
    Dim o           As Long
    If Not Intersect(Range("b97"), Target) Is Nothing Then
        Cancel = True
        o = 5 * Target.Row - 387
        xCellsA = o & ":" & o + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsP     As String
    Dim p           As Long
    If Not Intersect(Range("b101"), Target) Is Nothing Then
        Cancel = True
        p = 5 * Target.Row - 403
        xCellsA = p & ":" & p + 11
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsQ     As String
    Dim q           As Long
    If Not Intersect(Range("b114"), Target) Is Nothing Then
        Cancel = True
        q = 5 * Target.Row - 455
        xCellsA = q & ":" & q + 3
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsS     As String
    Dim s           As Long
    If Not Intersect(Range("b119"), Target) Is Nothing Then
        Cancel = True
        s = 5 * Target.Row - 475
        xCellsA = s & ":" & s + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsU     As String
    Dim u           As Long
    If Not Intersect(Range("b123"), Target) Is Nothing Then
        Cancel = True
        u = 5 * Target.Row - 491
        xCellsA = u & ":" & u + 6
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsV     As String
    Dim v           As Long
    If Not Intersect(Range("b131"), Target) Is Nothing Then
        Cancel = True
        v = 5 * Target.Row - 523
        xCellsA = v & ":" & v + 0
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsW     As String
    Dim w           As Long
    If Not Intersect(Range("b133"), Target) Is Nothing Then
        Cancel = True
        w = 5 * Target.Row - 531
        xCellsA = w & ":" & w + 12
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsX     As String
    Dim x           As Long
    If Not Intersect(Range("b147"), Target) Is Nothing Then
        Cancel = True
        x = 5 * Target.Row - 587
        xCellsA = x & ":" & x + 11
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsY     As String
    Dim y           As Long
    If Not Intersect(Range("b160"), Target) Is Nothing Then
        Cancel = True
        y = 5 * Target.Row - 639
        xCellsA = y & ":" & y + 5
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsZ     As String
    Dim z           As Long
    If Not Intersect(Range("b167"), Target) Is Nothing Then
        Cancel = True
        z = 5 * Target.Row - 667
        xCellsA = z & ":" & z + 3
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAB    As String
    Dim ab          As Long
    If Not Intersect(Range("b172"), Target) Is Nothing Then
        Cancel = True
        ab = 5 * Target.Row - 687
        xCellsA = ab & ":" & ab + 5
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAC    As String
    Dim ac          As Long
    If Not Intersect(Range("b179"), Target) Is Nothing Then
        Cancel = True
        ac = 5 * Target.Row - 715
        xCellsA = ac & ":" & ac + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAD    As String
    Dim ad          As Long
    If Not Intersect(Range("b183"), Target) Is Nothing Then
        Cancel = True
        ad = 5 * Target.Row - 731
        xCellsA = ad & ":" & ad + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAE    As String
    Dim ae          As Long
    If Not Intersect(Range("b187"), Target) Is Nothing Then
        Cancel = True
        ae = 5 * Target.Row - 747
        xCellsA = ae & ":" & ae + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAF    As String
    Dim af          As Long
    If Not Intersect(Range("b190"), Target) Is Nothing Then
        Cancel = True
        af = 5 * Target.Row - 759
        xCellsA = af & ":" & af + 7
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAG    As String
    Dim ag          As Long
    If Not Intersect(Range("b199"), Target) Is Nothing Then
        Cancel = True
        ag = 5 * Target.Row - 795
        xCellsA = ag & ":" & ag + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAH    As String
    Dim ah          As Long
    If Not Intersect(Range("b202"), Target) Is Nothing Then
        Cancel = True
        ah = 5 * Target.Row - 807
        xCellsA = ah & ":" & ah + 6
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAI    As String
    Dim ai          As Long
    If Not Intersect(Range("b213"), Target) Is Nothing Then
        Cancel = True
        ai = 5 * Target.Row - 851
        xCellsA = ai & ":" & ai + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAJ    As String
    Dim aj          As Long
    If Not Intersect(Range("b216"), Target) Is Nothing Then
        Cancel = True
        aj = 5 * Target.Row - 863
        xCellsA = aj & ":" & aj + 14
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAK    As String
    Dim ak          As Long
    If Not Intersect(Range("b232"), Target) Is Nothing Then
        Cancel = True
        ak = 5 * Target.Row - 927
        xCellsA = ak & ":" & ak + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAL    As String
    Dim al          As Long
    If Not Intersect(Range("b236"), Target) Is Nothing Then
        Cancel = True
        al = 5 * Target.Row - 943
        xCellsA = al & ":" & al + 7
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAM    As String
    Dim am          As Long
    If Not Intersect(Range("b245"), Target) Is Nothing Then
        Cancel = True
        am = 5 * Target.Row - 979
        xCellsA = am & ":" & am + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAMM   As String
    Dim amm         As Long
    If Not Intersect(Range("b248"), Target) Is Nothing Then
        Cancel = True
        amm = 5 * Target.Row - 991
        xCellsA = amm & ":" & amm + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAN    As String
    Dim an          As Long
    If Not Intersect(Range("b252"), Target) Is Nothing Then
        Cancel = True
        an = 5 * Target.Row - 1007
        xCellsA = an & ":" & an + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAO    As String
    Dim ao          As Long
    If Not Intersect(Range("b256"), Target) Is Nothing Then
        Cancel = True
        ao = 5 * Target.Row - 1023
        xCellsA = ao & ":" & ao + 2
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAP    As String
    Dim ap          As Long
    If Not Intersect(Range("b260"), Target) Is Nothing Then
        Cancel = True
        ap = 5 * Target.Row - 1039
        xCellsA = ap & ":" & ap + 0
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAQ    As String
    Dim aq          As Long
    If Not Intersect(Range("b262"), Target) Is Nothing Then
        Cancel = True
        aq = 5 * Target.Row - 1047
        xCellsA = aq & ":" & aq + 1
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAR    As String
    Dim ar          As Long
    If Not Intersect(Range("b265"), Target) Is Nothing Then
        Cancel = True
        ar = 5 * Target.Row - 1059
        xCellsA = ar & ":" & ar + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAT    As String
    Dim at          As Long
    If Not Intersect(Range("b271"), Target) Is Nothing Then
        Cancel = True
        at = 5 * Target.Row - 1083
        xCellsA = at & ":" & at + 0
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    Dim xCellsAU    As String
    Dim au          As Long
    If Not Intersect(Range("b273"), Target) Is Nothing Then
        Cancel = True
        au = 5 * Target.Row - 1091
        xCellsA = au & ":" & au + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    
    'Pre Job Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H14:H20,H22:H22,H25:H26,H28:H32,H34:H38,H40:H44,H46:H50,H52:H53,H55:H56"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    'Draft Job Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H61:H67,H69:H75,H77:H82,H84:H87,H89:H96,H98:H100,H102:H113,H115:H118,H20:H122,H124:H130,H132:H132,H134:H134,H136:H146,H148:H159,H161:H166,H168:H171,H173:H178,H180:H182,H184:H186,H188:H189,H191:H198,H200:H201,H203:H209"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    'Pre Reveiew Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H214:H215,H217:H217,H219:H219,H221:H231,H233:H235,H237:H244,H246:H247,H249:H251,H253:H255,H257:H259,H261:H261,H263:H264,H266:H270,H272:H272,H274:H278"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    'Review Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H282:H287"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    'OFS Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H290:H294"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 16 And Target.Row = 3 Then
        If Target.Value = "Company" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = True
            Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = False
        ElseIf Target.Value = "Trust, Partnership & Sole Trader" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = False
            Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
        ElseIf Target.Value = "All" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263,65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
        End If
    End If
    
End Sub

'Single-click
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

 

 

1 Reply

@rbalza 

Here is a shortened version of Worksheet_BeforeDoubleClick. The other procedure remains the same.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim arr1
    Dim arr2
    Dim i As Long
    Dim r As Long
    Dim xCells As String
    arr1 = Array(13, 21, 24, 27, 33, 39, 45, 51, 54, 60, 68, 76, 83, 88, 97, 101, 114, 119, 123, 131, 133, 147, _
        160, 167, 172, 179, 183, 187, 190, 199, 202, 213, 216, 232, 236, 245, 248, 252, 256, 260, 262, 265, 271, 273)
    arr2 = Array(6, 1, 1, 4, 4, 4, 4, 1, 1, 6, 6, 5, 3, 7, 2, 11, 3, 2, 6, 0, _
        12, 11, 5, 3, 5, 2, 2, 1, 7, 1, 6, 1, 14, 2, 7, 1, 2, 2, 2, 0, 1, 4, 0, 4)
    For i = 0 To UBound(arr1)
        r = arr1(i) + 1
        If Not Intersect(Range("B" & arr1(i)), Target) Is Nothing Then
            Cancel = True
            xCells = r & ":" & r + arr2(i)
            If Target.Value = "+" Then
                Rows(xCells).Hidden = False
                Target.Value = "-"
            Else
                Rows(xCells).Hidden = True
                Target.Value = "+"
            End If
        End If
        xCells = "H" & r & ":H" & r + arr2(i)
        If Not Intersect(Range(xCells), Target) Is Nothing Then
            Cancel = True
            Select Case Target.Value
                Case "X"
                    Target.Value = "P"
                Case "O"
                    Target.Value = "X"
                Case Else
                    Target.Value = "O"
            End Select
        End If
    Next i
    
    'Review Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H282:H287"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
    'OFS Process Tab - Status selection (Yes/No/NA)
    If Not Intersect(Range("H290:H294"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
End Sub