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

 

[DanteAmor]

I hope this helps you.

(No tested)

Sub macro()
Dim f as range
Set f = Range("B8:B66").find("X", , xlvalues, xlwhole)
If not f is nothing Then
    MsgBox "Positive result"
    Activesheet.unprotect
    Range("B10:M10").Locked = True
    Activesheet.protect
Else
    MsgBox "Negative result"
End If
End sub

 

[Pinaceous]

Hey Dante Amor,

That code does help and I do appreciate what you have posted.

What I am trying to accomplish is a little different.

Let me try to explain it a little differently. If an entry in column B, specifically over the range of B8:B66 provides for an entry with a positive result, then its respective row from column B to M gets locked.

For example, if an entry provided in cell B:23 yields a positive result, its row (row #23 ) from cell B:23 to M:23 gets locked.

Please let me know, if you can revise your code.

Thank you!
pinaceous

 

[DanteAmor]

Try this

Sub macro()
  Dim c As Range
  ActiveSheet.Unprotect
  For Each c In Range("B8:B66")
    If c = "X" Then Range("B" & c.Row & ":M" & c.Row).Locked = True
  Next
  ActiveSheet.Protect
End Sub

If "positive" you mean greater than 0, then change this line

If [COLOR=#0000ff]c = "X"[/COLOR] Then Range("B" & c.Row & ":M" & c.Row).Locked = True

to this

If [COLOR=#0000ff]c > 0[/COLOR] Then Range("B" & c.Row & ":M" & c.Row).Locked = True

 

[Pinaceous]

Hello DanteAmor,

I really like the code now!

It works wonderful!

How would you or where would you put in your code, if cell in Range ("B8:B66"), if it is locked to ignore this row?

This way it won't ping every entry that is entered in this range.

Many thanks,
pinaceous

 

[DanteAmor]

Try this

Sub macro()
  Dim c As Range, r As Range
  ActiveSheet.Unprotect
  Set r = Range("B8:B66")
  r.Locked = False
  For Each c In r
    If c = "X" Then Range("B" & c.Row & ":M" & c.Row).Locked = True
  Next
  ActiveSheet.Protect
End Sub

 

[Pinaceous]

Thank you Dante Amor! It looks really great ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Pinaceous]

Hi Dante Amor!

Now bringing it all together, I have the following code:

[COLOR=#222222][FONT=Verdana]Sub BTWDates2()[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]    Dim found AsBoolean[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim x AsVariant, d As Variant[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim c As Range,r As Range[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim cell AsRange[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim dateRng AsDate[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim sDate1 AsDate[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Dim sDate2 AsDate[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    sDate1 =#10/1/2019#[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    sDate2 =#9/30/2020#[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]    Set r =Range("B8:B66")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    r.Locked =False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    For Each c In r[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    If c = d ThenRange("B" & c.Row & ":M" & c.Row).Locked = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Next[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]        x =ActiveSheet.Range("B8:B66")[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]        For Each dIn x[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]            If d<> Empty Then[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]               Select Case d[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]                   Case sDate1 To sDate2[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]                       MsgBox d & " Within Date Range " & ActiveSheet.Name,vbInformation, "PPQ ProtectUpdate."[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]                        'If cell value within date range then lock row.[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]found = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]For Each cell In Range("B8:B66").Cells[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    If cell.Value =d Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        found =True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Next[/FONT][/COLOR]



[COLOR=#222222][FONT=Verdana]If found = True Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    MsgBox"Positive result"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]      'Dim c AsRange[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]  For Each c InRange("B8:B66")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        If c = dThen Range("B" & c.Row & ":M" & c.Row).Locked =True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        'If"positive" you mean greater than 0, then use thois line[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        'If c >0 Then Range("B" & c.Row & ":M" & c.Row).Locked= True[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]  Next[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    MsgBox"Negative result"[/FONT][/COLOR]

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

[COLOR=#222222][FONT=Verdana]                       'If cell value is not within date range then highlight cell unless typedwithin range.[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                       'Call HighlightCell[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                EndSelect[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Next[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana] End Sub[/FONT][/COLOR]

Now, I'm having trouble merging your code with what youposted in Post#6 :

[COLOR=#222222][FONT=Verdana]Sub macro()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  Dim c As Range, rAs Range[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] ActiveSheet.Unprotect[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  Set r =Range("B8:B66")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  r.Locked = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  For Each c In r[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    If c ="X" Then Range("B" & c.Row & ":M" &c.Row).Locked = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  Next[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] ActiveSheet.Protect[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

Can you please help me put these two codes together?

I’m experiencing difficulties.

Thank you!
Pinaceous

 

[DanteAmor]

I don't understand what you want to do with this macro: "BTWDates2"
Could you explain in detail and with examples what do you need?