Refactoring VBA code

A

apsblackhorse

New Member
Joined
Jul 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone, could someone help me refactor my code please. I am new on vba/macro coding. Hence, my coding skills doesn't that good. Wanted to learn to have an efficient macro/vba scripts. Attached were the codes. Thanks in advance.


VBA Code: 
' 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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
SampleData.xlsm
BCDEFGHIJKLMP
11PROCESS 112
12Notes
13-Task 1Completed0TF10.000.00
14List 1
15List 2
16List 3
17List 4
18List 5
19List 6
20List 7
21-Task 22 Outstanding2CSC10.008.50
22List 1O
23List 2O
24-Task 3Complete0TF0.00
25List 1X
26List 2X
27-Task 45 Outstanding5CS0.00
28List 1O
29List 2O
30List 3O
31List 4O
32List 5O
33-Task 5Complete0CSC2.008.50
34List 1
35List 2
36List 3
37List 4
38List 5
Annual
Cell Formulas
RangeFormula
H11H11=COUNTIF(H13:H51,"O")
G13G13=IF(H13>0,H13&" Outstanding","Completed")
H13H13=COUNTIF(H14:H20,"O")
K13,K27,K24K13=IFERROR(VLOOKUP(I13,$K$5:$M$7,3,FALSE),0)
G21,G33,G27,G24G21=IF(H21>0,H21&" Outstanding","Complete")
H21,H24H21=COUNTIF(H22:H23,"O")
H27,H33H27=COUNTIF(H28:H32,"O")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G13Expression=OR($J$13>0)textNO
G13Cell Valuecontains "Outstanding"textNO
G24Expression=OR($I$24="",$J$24=0)textNO
G21Expression=OR($I$21="",$J$21=0)textNO
G21Expression=OR($J$21>0)textNO
G13Expression=OR($I$13="",$J$13=0)textNO
H14:H20,H28:H32,H25:H26,H22:H23,H52:H53,H46:H50,H40:H44,H34:H38Cellcontains a blank value textNO
H14:H20,H28:H32,H25:H26,H22:H23,H52:H53,H46:H50,H40:H44,H34:H38Cell Valuecontains "X"textNO
G12,G59:G60,G83Cell Valuecontains "Outstanding"textNO
G12,G59:G60,G83Cell Valuecontains "Complete"textNO
Cells with Data Validation
CellAllowCriteria
H13Any value
H14:H20Any value
H21Any value
H22:H23Any value
H24Any value
H25:H26Any value
H27Any value
H28:H32Any value
H33Any value
H34:H38Any value
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
207
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
102
Fluff
Fluff
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
235
Joe4
Joe4
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
195
dmt32
D
excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
478
excelbytes
excelbytes

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top