VBA row ?

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Thank you Dante Amor! It looks really great ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,785
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top