Data Validation in VBA Code

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
626
Office Version
  1. 2016
Platform
  1. Windows
hello yall Im tring to do a DV in VBA code where if A1=Yes then value in D1 must be between 1-120 and if value in A1=No then value in D1 must be between 121 - 150

Im thinking I need a DV in VBA Code. I already have a DV for D1 so I prefer a VBA.

Any suggestion is greatly appreciative.


Thanks
 
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, [T2]) Is Nothing And UCase([T2]) = "YES" Then _
    Sheets("CSA1").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T3]) Is Nothing And UCase([T3]) = "YES" Then _
    Sheets("CSA2").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T4]) Is Nothing And UCase([T4]) = "YES" Then
    Sheets("CSA3").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T5]) Is Nothing And UCase([T5]) = "YES" Then _
    Sheets("CSA4").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T6]) Is Nothing And UCase([T6]) = "YES" Then _
    Sheets("CSA5").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T7]) Is Nothing And UCase([T7]) = "YES" Then _
    Sheets("CSA6").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T8]) Is Nothing And UCase([T8]) = "YES" Then _
    Sheets("CSA7").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T9]) Is Nothing And UCase([T9]) = "YES" Then _
    Sheets("CSA8").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T10]) Is Nothing And UCase([T10]) = "YES" Then _
    Sheets("CSA9").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T11]) Is Nothing And UCase([T11]) = "YES" Then _
    Sheets("CSA10").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T12]) Is Nothing And UCase([T12]) = "YES" Then _
    Sheets("CSA11").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T13]) Is Nothing And UCase([T13]) = "YES" Then _
    Sheets("CSA12").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T14]) Is Nothing And UCase([T14]) = "YES" Then _
    Sheets("CSA13").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T15]) Is Nothing And UCase([T15]) = "YES" Then _
    Sheets("CSA14").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T16]) Is Nothing And UCase([T16]) = "YES" Then _
    Sheets("CSA15").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T17]) Is Nothing And UCase([T17]) = "YES" Then _
    Sheets("CSA16").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T18]) Is Nothing And UCase([T18]) = "YES" Then _
    Sheets("CSA17").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T19]) Is Nothing And UCase([T19]) = "YES" Then _
    Sheets("CSA18").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T20]) Is Nothing And UCase([T20]) = "YES" Then _
    Sheets("CSA19").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T21]) Is Nothing And UCase([T21]) = "YES" Then _
    Sheets("CSA20").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T22]) Is Nothing And UCase([T22]) = "YES" Then _
    Sheets("CSA21").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T23]) Is Nothing And UCase([T23]) = "YES" Then _
    Sheets("CSA22").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T24]) Is Nothing And UCase([T24]) = "YES" Then _
    Sheets("CSA23").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T25]) Is Nothing And UCase([T25]) = "YES" Then _
    Sheets("CSA24").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T26]) Is Nothing And UCase([T26]) = "YES" Then _
    Sheets("CSA25").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T27]) Is Nothing And UCase([T27]) = "YES" Then _
    Sheets("CSA26").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T28]) Is Nothing And UCase([T28]) = "YES" Then _
    Sheets("CSA27").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T29]) Is Nothing And UCase([T29]) = "YES" Then _
    Sheets("CSA28").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T30]) Is Nothing And UCase([T30]) = "YES" Then _
    Sheets("CSA29").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T31]) Is Nothing And UCase([T31]) = "YES" Then _
    Sheets("CSA30").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T32]) Is Nothing And UCase([T32]) = "YES" Then _
    Sheets("CSA31").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T33]) Is Nothing And UCase([T33]) = "YES" Then _
    Sheets("CSA32").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T34]) Is Nothing And UCase([T34]) = "YES" Then _
    Sheets("CSA33").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T35]) Is Nothing And UCase([T35]) = "YES" Then _
    Sheets("CSA34").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T36]) Is Nothing And UCase([T36]) = "YES" Then _
    Sheets("CSA35").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T37]) Is Nothing And UCase([T37]) = "YES" Then _
    Sheets("CSA36").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T38]) Is Nothing And UCase([T38]) = "YES" Then _
    Sheets("CSA37").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T39]) Is Nothing And UCase([T39]) = "YES" Then _
    Sheets("CSA38").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T40]) Is Nothing And UCase([T40]) = "YES" Then _
    Sheets("CSA39").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T41]) Is Nothing And UCase([T41]) = "YES" Then _
    Sheets("CSA40").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T42]) Is Nothing And UCase([T42]) = "YES" Then _
    Sheets("CSA41").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T43]) Is Nothing And UCase([T43]) = "YES" Then _
    Sheets("CSA42").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T44]) Is Nothing And UCase([T44]) = "YES" Then _
    Sheets("CSA43").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T45]) Is Nothing And UCase([T45]) = "YES" Then _
    Sheets("CSA44").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T46]) Is Nothing And UCase([T46]) = "YES" Then _
    Sheets("CSA45").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T47]) Is Nothing And UCase([T47]) = "YES" Then _
    Sheets("CSA46").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T48]) Is Nothing And UCase([T48]) = "YES" Then _
    Sheets("CSA47").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T49]) Is Nothing And UCase([T49]) = "YES" Then _
    Sheets("CSA48").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T50]) Is Nothing And UCase([T50]) = "YES" Then _
    Sheets("CSA49").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T51]) Is Nothing And UCase([T51]) = "YES" Then _
    Sheets("CSA50").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T52]) Is Nothing And UCase([T52]) = "YES" Then _
    Sheets("CSA51").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T53]) Is Nothing And UCase([T53]) = "YES" Then _
    Sheets("CSA52").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T54]) Is Nothing And UCase([T54]) = "YES" Then _
    Sheets("CSA53").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T55]) Is Nothing And UCase([T55]) = "YES" Then _
    Sheets("CSA54").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T56]) Is Nothing And UCase([T56]) = "YES" Then _
    Sheets("CSA55").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T57]) Is Nothing And UCase([T57]) = "YES" Then _
    Sheets("CSA56").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T58]) Is Nothing And UCase([T58]) = "YES" Then _
    Sheets("CSA57").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T59]) Is Nothing And UCase([T59]) = "YES" Then _
    Sheets("CSA58").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T60]) Is Nothing And UCase([T60]) = "YES" Then _
    Sheets("CSA59").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T61]) Is Nothing And UCase([T61]) = "YES" Then _
    Sheets("CSA60").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T62]) Is Nothing And UCase([T62]) = "YES" Then _
    Sheets("CSA61").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T63]) Is Nothing And UCase([T63]) = "YES" Then _
    Sheets("CSA62").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T64]) Is Nothing And UCase([T64]) = "YES" Then _
    Sheets("CSA63").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T65]) Is Nothing And UCase([T65]) = "YES" Then _
    Sheets("CSA64").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T66]) Is Nothing And UCase([T66]) = "YES" Then _
    Sheets("CSA65").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T67]) Is Nothing And UCase([T67]) = "YES" Then _
    Sheets("CSA66").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T68]) Is Nothing And UCase([T68]) = "YES" Then _
    Sheets("CSA67").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T69]) Is Nothing And UCase([T69]) = "YES" Then _
    Sheets("CSA68").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T70]) Is Nothing And UCase([T70]) = "YES" Then _
    Sheets("CSA69").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T71]) Is Nothing And UCase([T71]) = "YES" Then _
    Sheets("CSA70").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T72]) Is Nothing And UCase([T72]) = "YES" Then _
    Sheets("CSA71").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T73]) Is Nothing And UCase([T73]) = "YES" Then _
    Sheets("CSA72").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T74]) Is Nothing And UCase([T74]) = "YES" Then _
    Sheets("CSA73").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T75]) Is Nothing And UCase([T75]) = "YES" Then _
    Sheets("CSA74").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T76]) Is Nothing And UCase([T76]) = "YES" Then _
    Sheets("CSA75").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T77]) Is Nothing And UCase([T77]) = "YES" Then _
    Sheets("CSA76").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T78]) Is Nothing And UCase([T78]) = "YES" Then _
    Sheets("CSA77").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T79]) Is Nothing And UCase([T79]) = "YES" Then _
    Sheets("CSA78").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T80]) Is Nothing And UCase([T80]) = "YES" Then _
    Sheets("CSA79").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T81]) Is Nothing And UCase([T81]) = "YES" Then _
    Sheets("CSA80").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T82]) Is Nothing And UCase([T82]) = "YES" Then _
    Sheets("CSA81").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T83]) Is Nothing And UCase([T83]) = "YES" Then _
    Sheets("CSA82").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T84]) Is Nothing And UCase([T84]) = "YES" Then _
    Sheets("CSA83").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T85]) Is Nothing And UCase([T85]) = "YES" Then _
    Sheets("CSA84").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T86]) Is Nothing And UCase([T86]) = "YES" Then _
    Sheets("CSA85").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T87]) Is Nothing And UCase([T87]) = "YES" Then _
    Sheets("CSA86").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T88]) Is Nothing And UCase([T88]) = "YES" Then _
    Sheets("CSA87").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T89]) Is Nothing And UCase([T89]) = "YES" Then _
    Sheets("CSA88").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T90]) Is Nothing And UCase([T90]) = "YES" Then _
    Sheets("CSA89").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T91]) Is Nothing And UCase([T91]) = "YES" Then _
    Sheets("CSA90").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T92]) Is Nothing And UCase([T92]) = "YES" Then _
    Sheets("CSA91").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T93]) Is Nothing And UCase([T93]) = "YES" Then _
    Sheets("CSA92").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T94]) Is Nothing And UCase([T94]) = "YES" Then _
    Sheets("CSA93").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T95]) Is Nothing And UCase([T95]) = "YES" Then _
    Sheets("CSA94").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T96]) Is Nothing And UCase([T96]) = "YES" Then _
    Sheets("CSA95").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T97]) Is Nothing And UCase([T97]) = "YES" Then _
    Sheets("CSA96").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T98]) Is Nothing And UCase([T98]) = "YES" Then _
    Sheets("CSA97").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T99]) Is Nothing And UCase([T99]) = "YES" Then _
    Sheets("CSA98").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T100]) Is Nothing And UCase([T100]) = "YES" Then _
    Sheets("CSA99").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T101]) Is Nothing And UCase([T101]) = "YES" Then _
    Sheets("CSA100").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T102]) Is Nothing And UCase([T102]) = "YES" Then _
    Sheets("CSA101").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T103]) Is Nothing And UCase([T103]) = "YES" Then _
    Sheets("CSA102").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T104]) Is Nothing And UCase([T104]) = "YES" Then _
    Sheets("CSA103").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T105]) Is Nothing And UCase([T105]) = "YES" Then _
    Sheets("CSA104").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T106]) Is Nothing And UCase([T106]) = "YES" Then _
    Sheets("CSA105").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T107]) Is Nothing And UCase([T107]) = "YES" Then _
    Sheets("CSA106").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T108]) Is Nothing And UCase([T108]) = "YES" Then _
    Sheets("CSA107").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T109]) Is Nothing And UCase([T109]) = "YES" Then _
    Sheets("CSA108").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T110]) Is Nothing And UCase([T110]) = "YES" Then _
    Sheets("CSA109").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T111]) Is Nothing And UCase([T111]) = "YES" Then _
    Sheets("CSA110").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T112]) Is Nothing And UCase([T112]) = "YES" Then _
    Sheets("CSA111").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T113]) Is Nothing And UCase([T113]) = "YES" Then _
    Sheets("CSA112").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T114]) Is Nothing And UCase([T114]) = "YES" Then _
    Sheets("CSA113").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T115]) Is Nothing And UCase([T115]) = "YES" Then _
    Sheets("CSA114").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T116]) Is Nothing And UCase([T116]) = "YES" Then _
    Sheets("CSA115").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T117]) Is Nothing And UCase([T117]) = "YES" Then _
    Sheets("CSA116").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T118]) Is Nothing And UCase([T118]) = "YES" Then _
    Sheets("CSA117").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T119]) Is Nothing And UCase([T119]) = "YES" Then _
    Sheets("CSA118").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T120]) Is Nothing And UCase([T120]) = "YES" Then _
    Sheets("CSA119").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T121]) Is Nothing And UCase([T121]) = "YES" Then _
    Sheets("CSA120").Range("A3:A122,a124:a153").ClearContents
End If
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, [T2]) Is Nothing And UCase([T2]) = "YES" Then _
    Sheets("CSA1").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T3]) Is Nothing And UCase([T3]) = "YES" Then _
    Sheets("CSA2").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T4]) Is Nothing And UCase([T4]) = "YES" Then
    Sheets("CSA3").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T5]) Is Nothing And UCase([T5]) = "YES" Then _
    Sheets("CSA4").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T6]) Is Nothing And UCase([T6]) = "YES" Then _
    Sheets("CSA5").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T7]) Is Nothing And UCase([T7]) = "YES" Then _
    Sheets("CSA6").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T8]) Is Nothing And UCase([T8]) = "YES" Then _
    Sheets("CSA7").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T9]) Is Nothing And UCase([T9]) = "YES" Then _
    Sheets("CSA8").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T10]) Is Nothing And UCase([T10]) = "YES" Then _
    Sheets("CSA9").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T11]) Is Nothing And UCase([T11]) = "YES" Then _
    Sheets("CSA10").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T12]) Is Nothing And UCase([T12]) = "YES" Then _
    Sheets("CSA11").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T13]) Is Nothing And UCase([T13]) = "YES" Then _
    Sheets("CSA12").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T14]) Is Nothing And UCase([T14]) = "YES" Then _
    Sheets("CSA13").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T15]) Is Nothing And UCase([T15]) = "YES" Then _
    Sheets("CSA14").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T16]) Is Nothing And UCase([T16]) = "YES" Then _
    Sheets("CSA15").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T17]) Is Nothing And UCase([T17]) = "YES" Then _
    Sheets("CSA16").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T18]) Is Nothing And UCase([T18]) = "YES" Then _
    Sheets("CSA17").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T19]) Is Nothing And UCase([T19]) = "YES" Then _
    Sheets("CSA18").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T20]) Is Nothing And UCase([T20]) = "YES" Then _
    Sheets("CSA19").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T21]) Is Nothing And UCase([T21]) = "YES" Then _
    Sheets("CSA20").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T22]) Is Nothing And UCase([T22]) = "YES" Then _
    Sheets("CSA21").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T23]) Is Nothing And UCase([T23]) = "YES" Then _
    Sheets("CSA22").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T24]) Is Nothing And UCase([T24]) = "YES" Then _
    Sheets("CSA23").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T25]) Is Nothing And UCase([T25]) = "YES" Then _
    Sheets("CSA24").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T26]) Is Nothing And UCase([T26]) = "YES" Then _
    Sheets("CSA25").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T27]) Is Nothing And UCase([T27]) = "YES" Then _
    Sheets("CSA26").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T28]) Is Nothing And UCase([T28]) = "YES" Then _
    Sheets("CSA27").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T29]) Is Nothing And UCase([T29]) = "YES" Then _
    Sheets("CSA28").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T30]) Is Nothing And UCase([T30]) = "YES" Then _
    Sheets("CSA29").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T31]) Is Nothing And UCase([T31]) = "YES" Then _
    Sheets("CSA30").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T32]) Is Nothing And UCase([T32]) = "YES" Then _
    Sheets("CSA31").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T33]) Is Nothing And UCase([T33]) = "YES" Then _
    Sheets("CSA32").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T34]) Is Nothing And UCase([T34]) = "YES" Then _
    Sheets("CSA33").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T35]) Is Nothing And UCase([T35]) = "YES" Then _
    Sheets("CSA34").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T36]) Is Nothing And UCase([T36]) = "YES" Then _
    Sheets("CSA35").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T37]) Is Nothing And UCase([T37]) = "YES" Then _
    Sheets("CSA36").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T38]) Is Nothing And UCase([T38]) = "YES" Then _
    Sheets("CSA37").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T39]) Is Nothing And UCase([T39]) = "YES" Then _
    Sheets("CSA38").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T40]) Is Nothing And UCase([T40]) = "YES" Then _
    Sheets("CSA39").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T41]) Is Nothing And UCase([T41]) = "YES" Then _
    Sheets("CSA40").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T42]) Is Nothing And UCase([T42]) = "YES" Then _
    Sheets("CSA41").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T43]) Is Nothing And UCase([T43]) = "YES" Then _
    Sheets("CSA42").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T44]) Is Nothing And UCase([T44]) = "YES" Then _
    Sheets("CSA43").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T45]) Is Nothing And UCase([T45]) = "YES" Then _
    Sheets("CSA44").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T46]) Is Nothing And UCase([T46]) = "YES" Then _
    Sheets("CSA45").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T47]) Is Nothing And UCase([T47]) = "YES" Then _
    Sheets("CSA46").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T48]) Is Nothing And UCase([T48]) = "YES" Then _
    Sheets("CSA47").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T49]) Is Nothing And UCase([T49]) = "YES" Then _
    Sheets("CSA48").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T50]) Is Nothing And UCase([T50]) = "YES" Then _
    Sheets("CSA49").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T51]) Is Nothing And UCase([T51]) = "YES" Then _
    Sheets("CSA50").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T52]) Is Nothing And UCase([T52]) = "YES" Then _
    Sheets("CSA51").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T53]) Is Nothing And UCase([T53]) = "YES" Then _
    Sheets("CSA52").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T54]) Is Nothing And UCase([T54]) = "YES" Then _
    Sheets("CSA53").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T55]) Is Nothing And UCase([T55]) = "YES" Then _
    Sheets("CSA54").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T56]) Is Nothing And UCase([T56]) = "YES" Then _
    Sheets("CSA55").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T57]) Is Nothing And UCase([T57]) = "YES" Then _
    Sheets("CSA56").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T58]) Is Nothing And UCase([T58]) = "YES" Then _
    Sheets("CSA57").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T59]) Is Nothing And UCase([T59]) = "YES" Then _
    Sheets("CSA58").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T60]) Is Nothing And UCase([T60]) = "YES" Then _
    Sheets("CSA59").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T61]) Is Nothing And UCase([T61]) = "YES" Then _
    Sheets("CSA60").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T62]) Is Nothing And UCase([T62]) = "YES" Then _
    Sheets("CSA61").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T63]) Is Nothing And UCase([T63]) = "YES" Then _
    Sheets("CSA62").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T64]) Is Nothing And UCase([T64]) = "YES" Then _
    Sheets("CSA63").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T65]) Is Nothing And UCase([T65]) = "YES" Then _
    Sheets("CSA64").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T66]) Is Nothing And UCase([T66]) = "YES" Then _
    Sheets("CSA65").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T67]) Is Nothing And UCase([T67]) = "YES" Then _
    Sheets("CSA66").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T68]) Is Nothing And UCase([T68]) = "YES" Then _
    Sheets("CSA67").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T69]) Is Nothing And UCase([T69]) = "YES" Then _
    Sheets("CSA68").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T70]) Is Nothing And UCase([T70]) = "YES" Then _
    Sheets("CSA69").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T71]) Is Nothing And UCase([T71]) = "YES" Then _
    Sheets("CSA70").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T72]) Is Nothing And UCase([T72]) = "YES" Then _
    Sheets("CSA71").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T73]) Is Nothing And UCase([T73]) = "YES" Then _
    Sheets("CSA72").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T74]) Is Nothing And UCase([T74]) = "YES" Then _
    Sheets("CSA73").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T75]) Is Nothing And UCase([T75]) = "YES" Then _
    Sheets("CSA74").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T76]) Is Nothing And UCase([T76]) = "YES" Then _
    Sheets("CSA75").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T77]) Is Nothing And UCase([T77]) = "YES" Then _
    Sheets("CSA76").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T78]) Is Nothing And UCase([T78]) = "YES" Then _
    Sheets("CSA77").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T79]) Is Nothing And UCase([T79]) = "YES" Then _
    Sheets("CSA78").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T80]) Is Nothing And UCase([T80]) = "YES" Then _
    Sheets("CSA79").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T81]) Is Nothing And UCase([T81]) = "YES" Then _
    Sheets("CSA80").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T82]) Is Nothing And UCase([T82]) = "YES" Then _
    Sheets("CSA81").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T83]) Is Nothing And UCase([T83]) = "YES" Then _
    Sheets("CSA82").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T84]) Is Nothing And UCase([T84]) = "YES" Then _
    Sheets("CSA83").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T85]) Is Nothing And UCase([T85]) = "YES" Then _
    Sheets("CSA84").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T86]) Is Nothing And UCase([T86]) = "YES" Then _
    Sheets("CSA85").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T87]) Is Nothing And UCase([T87]) = "YES" Then _
    Sheets("CSA86").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T88]) Is Nothing And UCase([T88]) = "YES" Then _
    Sheets("CSA87").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T89]) Is Nothing And UCase([T89]) = "YES" Then _
    Sheets("CSA88").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T90]) Is Nothing And UCase([T90]) = "YES" Then _
    Sheets("CSA89").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T91]) Is Nothing And UCase([T91]) = "YES" Then _
    Sheets("CSA90").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T92]) Is Nothing And UCase([T92]) = "YES" Then _
    Sheets("CSA91").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T93]) Is Nothing And UCase([T93]) = "YES" Then _
    Sheets("CSA92").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T94]) Is Nothing And UCase([T94]) = "YES" Then _
    Sheets("CSA93").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T95]) Is Nothing And UCase([T95]) = "YES" Then _
    Sheets("CSA94").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T96]) Is Nothing And UCase([T96]) = "YES" Then _
    Sheets("CSA95").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T97]) Is Nothing And UCase([T97]) = "YES" Then _
    Sheets("CSA96").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T98]) Is Nothing And UCase([T98]) = "YES" Then _
    Sheets("CSA97").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T99]) Is Nothing And UCase([T99]) = "YES" Then _
    Sheets("CSA98").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T100]) Is Nothing And UCase([T100]) = "YES" Then _
    Sheets("CSA99").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T101]) Is Nothing And UCase([T101]) = "YES" Then _
    Sheets("CSA100").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T102]) Is Nothing And UCase([T102]) = "YES" Then _
    Sheets("CSA101").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T103]) Is Nothing And UCase([T103]) = "YES" Then _
    Sheets("CSA102").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T104]) Is Nothing And UCase([T104]) = "YES" Then _
    Sheets("CSA103").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T105]) Is Nothing And UCase([T105]) = "YES" Then _
    Sheets("CSA104").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T106]) Is Nothing And UCase([T106]) = "YES" Then _
    Sheets("CSA105").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T107]) Is Nothing And UCase([T107]) = "YES" Then _
    Sheets("CSA106").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T108]) Is Nothing And UCase([T108]) = "YES" Then _
    Sheets("CSA107").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T109]) Is Nothing And UCase([T109]) = "YES" Then _
    Sheets("CSA108").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T110]) Is Nothing And UCase([T110]) = "YES" Then _
    Sheets("CSA109").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T111]) Is Nothing And UCase([T111]) = "YES" Then _
    Sheets("CSA110").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T112]) Is Nothing And UCase([T112]) = "YES" Then _
    Sheets("CSA111").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T113]) Is Nothing And UCase([T113]) = "YES" Then _
    Sheets("CSA112").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T114]) Is Nothing And UCase([T114]) = "YES" Then _
    Sheets("CSA113").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T115]) Is Nothing And UCase([T115]) = "YES" Then _
    Sheets("CSA114").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T116]) Is Nothing And UCase([T116]) = "YES" Then _
    Sheets("CSA115").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T117]) Is Nothing And UCase([T117]) = "YES" Then _
    Sheets("CSA116").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T118]) Is Nothing And UCase([T118]) = "YES" Then _
    Sheets("CSA117").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T119]) Is Nothing And UCase([T119]) = "YES" Then _
    Sheets("CSA118").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T120]) Is Nothing And UCase([T120]) = "YES" Then _
    Sheets("CSA119").Range("A3:A122,a124:a153").ClearContents
If Not Intersect(Target, [T121]) Is Nothing And UCase([T121]) = "YES" Then _
    Sheets("CSA120").Range("A3:A122,a124:a153").ClearContents
End If
 
Upvote 0
Firstly disable both subs named Worksheet_Change by renaming them differently
eg
Private Sub Xorksheet_Change(ByVal Target As Range)
Private Sub Zorksheet_Change(ByVal Target As Range)

Secondly test replacement for the last one you posted

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T1:T121")) Is Nothing Then
        Const Rng = "A3:A122,a124:a153"
        Dim Sh As Worksheet
        Set Sh = Sheets("CSA" & (Target.Row - 1))
        If UCase(Target) = "YES" Then Sh.Range(Rng).ClearContents
    End If
End Sub
 
Upvote 0
Thirdly

My earlier sub modified to make it consistent with the one posted in post#14
- I think target range should be rows 2 to 121 (like the other one)
- I assume that your DV forces user to put in integers between 1 and 150
- VBA slimmed it down to avoid repeating code

You may want to test it - remember to disable the other one with same name
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
        Dim msg As String
        Application.EnableEvents = False
        With Target
            If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
            If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                If msg <> "" Then
                    .ClearContents
                    MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                    .Select
                End If
        End With
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Finally

Combine like this and delete all the other versions!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T1:T121")) Is Nothing Then
        Const Rng = "A3:A122,a124:a153"
        Dim Sh As Worksheet
        Set Sh = Sheets("CSA" & (Target.Row - 1))
        If UCase(Target) = "YES" Then Sh.Range(Rng).ClearContents
    End If

    If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
        Dim msg As String
        Application.EnableEvents = False
        With Target
            If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
            If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                If msg <> "" Then
                    .ClearContents
                    MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                    .Select
                End If
        End With
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
The code to Clear CSA sheets works great and much effective.

For the Second IF statement How does it check Column A for "YES" or "NO" because its still allowing any values and not .ClearContents or populating the Error Msg.


Man I really Really Appreciate you taking the time help me.

Code:
    If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
        Dim msg As String
        Application.EnableEvents = False
        With Target
            If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
            If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                If msg <> "" Then
                    .ClearContents
                    MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                    .Select
                End If
        End With
        Application.EnableEvents = True
    End If
End Sub
[/QUOTE]
 
Upvote 0
It works for me
- something is different in your sheet or you are expecting something different to what I am expecting to happen
- you confirmed that the code was working correctly originally and it is doing EXACTLY what it was doing then (just looks a little different)
- originally when value in D1 was changed it looked to cell A1 to see if yes or no before testing for over and under 120

EXPLANATION

Macro is triggered when any cell in range D2:D121 is changed. So if the cell being amended is D4 then TARGET = Range("D4") ...

Code:
With [COLOR=#ff0000]Target[/COLOR]
If UCase([COLOR=#ff0000].Offset(, -3[/COLOR])) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"

is same as ...
Code:
If UCase([COLOR=#ff0000]TARGET.Offset(, -3)[/COLOR]) = "YES" And TARGET.Value > 120 Then ...

and Target is D4, so is same as ...
Code:
If UCase([COLOR=#ff0000]Range("D4").Offset(, -3)[/COLOR]) = "YES" And Range("D4").Value > 120 Then ...

and D4 offset 3 columns to the left is A4 ...
Code:
If UCase([COLOR=#ff0000]Range("A4")[/COLOR]) = "YES" And Range("D4").Value > 120 Then ...
Q1. Is there only ONE macro named Worksheet_Change and is it in the correct sheet module?

Q2. I have already asked this question previously WHAT IS YOUR DATA VALIDATION RULE IN COLUMN D? Is user forced to select a value?

Q3. Is there anything incorrect in my logic?
 
Last edited:
Upvote 0
Q1. Is there only ONE macro named Worksheet_Change and is it in the correct sheet module? YES
Q2. I have already asked this question previously WHAT IS YOUR DATA VALIDATION RULE IN COLUMN D? Is user forced to select a value?
CODE IN DV IS:
Code:
=COUNTIF($E$2:E2,D5)=0
Than dragged it to D121 so that numbers in a cell are not duplicate form any previous cell in column E


Q3. Is there anything incorrect in my logic?
I started a Brand New Workbook and inputted the code in the Sheet1 (Sheet1) under Microsoft Excel Object and Still not working. I Certain that the code you gave is correct just do not know whats going on.
 
Upvote 0
=COUNTIF($E$2:E2,D5)=0

What is in column E?
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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