VBA row ?

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
1,124
Office Version
  1. 365
Platform
  1. Windows
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"):

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


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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I hope this helps you.

(No tested)
Code:
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
 
Upvote 0
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
 
Upvote 0
Try this

Code:
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
Code:
If [COLOR=#0000ff]c = "X"[/COLOR] Then Range("B" & c.Row & ":M" & c.Row).Locked = True

to this
Code:
If [COLOR=#0000ff]c > 0[/COLOR] Then Range("B" & c.Row & ":M" & c.Row).Locked = True
 
Upvote 0
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
 
Upvote 0
Try this

Code:
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
 
Upvote 0
Hi Dante Amor!

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


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 :

Code:
[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
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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