VBA row ?

[Pinaceous]

Hi All,

I'm trying to out a condition whereby if my sub produces a "Positive result" then that row gets locked.

I'm working with Column B in range of ("B8:B66"):

x = ActiveSheet.Range("B8:B66")

found = True
For Each cell In Range("B8:B66").Cells
    If cell.Value = "X" Then
        found = True
    End If
Next
If found = True Then
    MsgBox "Positive result"
    
    'Range("B10:M10").Locked = True
    
Else
    MsgBox "Negative result"
End If

So, here if there is a "Positive result" it will lock row 10.

But now how do I tell the code, if entry of row whatever, for example Row 7 to 66, if it produces a "Positive result" its row becomes locked.

Please let me know, if you have any suggestions that I can toy around with.

Thank you,
pinaceous

 

[Pinaceous]

Hi DanteAmor,

Okay, I have taken your code in Post#2 that finds a “PositiveResult”.

This “Positive Result” is a date in between sDate1 and sDate2.

If the Sub BTWDates2 finds a “Positive Result” it then locks that row.

The Sub BTWDates2 will be run after every entry provided in the range (“B8:B66”).

If the entry is correctly provided by the user, being a date, it will then lock that rows entry.

There are will be numerous entries done correctly, so there should be numerous rows with locked cells.

I was hoping to achieve by your Post#6 code to skip all of locked rows.

So, is there a way to combine my working code BTWDates2 with your code from Post#6, which allows for locked rows to be skipped?

Please let me know, if this is clear or if you need further explanation.

Thank you,
Pinaceous

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub BTWDates2()[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.Unprotect[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim foundAsBoolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim x AsVariant, dAs Variant[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim c As Range,rAs Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim cell AsRange[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim dateRng AsDate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sDate1 AsDate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sDate2 AsDate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sDate1=#10/1/2019#[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sDate2=#9/30/2020#[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]        x=ActiveSheet.Range("B8:B66")[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]        For Each dIn x[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]            Ifd<> Empty Then[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]               SelectCase d[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]                  Case sDate1 To sDate2[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]                      MsgBox d & " Within Date Range " & ActiveSheet.Name,vbInformation,"PPQ ProtectUpdate."[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]found = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For Each cell In Range("B8:B66").Cells[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If cell.Value =dThen[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        found =True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]If found = True Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   MsgBox"Positive result"[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]  For Each cInRange("B8:B66")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        If c = dThenRange("B" & c.Row & ":M" & c.Row).Locked =True[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]  Next[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   MsgBox"Negative result"[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                  Case Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                      MsgBox d & " Out of Date Specified Range "&ActiveSheet.Name, vbInformation, "PPQ ProtectUpdate."[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]               EndSelect[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Next[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.Protect[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000] End Sub[/COLOR][/SIZE][/FONT]

 

[DanteAmor]

Sorry, but I still don't understand what you need.
We better start from the beginning, forget your code, forget my code and explain what you need using real examples.

 

[Pinaceous]

Hi Dante Amor, sorry about that! If in starting from the beginning, I'd better start a new thread. People are going to get up tight. Thank you, Paul