Solved: Unlock Row If cell Equals Specific Value

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to incorporate the following code in my 'Workbook on Open' event.

[vba]If Range("B7:B").Value > 0 And .Value <> "Enter your name" Then
Set Rng1 = Application.Union(Cells(Target.Row, "C").Resize(, 28), Cells(Target.Row, "AE"))
Rng1.Locked = False
End If
[/vba]

The problem I'm having is that when I run this I receive 'compile error'

What I'm trying to achieve is if column B, starting at row 7 is populated and is not like "Enter your name", then unlock the row from columns C:AE, but I'm not quite sure where I've gone wrong.

I just wondered whether someone may be able to look at this please and let me knwo where I'm going wrong.

Many thanks and kind regards

[SamT]

Since "B" might have an empty last cell, use a differetn column to find LAst Row

[VBA]Dim Cel As Range
For Each Cel In Range("B7:B" & LastRow)

If Cel <> "" And Cel <> "Enter your name" Then
Cel.Offset(0, 1).Resize(0, LastCol - 3).Locked = False
End If[/VBA]

[hobbiton73]

Hi @SamT, thank you for taking the time to reply to my post and for the solution.

I'm using this code in a project for work, so I'll take this with me and let you know how i get on after the weekend.

Once again many thanks and kind regards

Chris

[hobbiton73]

Hi @SamT, as promised I took the solution you kindly provided with me to work.

However, unfortunately I was unable to get this to work.

When I ran the code it highlighted 'Last Row' and 'LastCol' as being undefined.

So I added two variables as follows:

Dim LastRow as Long and,
Dim LastCol as Long.

In additon to this I then added a Next Loop to the end of the statement.

It's perhaps something I've done wrong, but when tried the run the script I received a 'Compile' error message.

Could you tell me please have you any ideas on where I may have gone wrong?

Many thanks and kind regards

Chris

[hobbiton73]

Hi SamT, as promised I took the solution you kindly provided to work with me, but unfortunately I was unable to get the script to work.

When I initially ran the script the debug highlighted 'LastRow' and 'LastCol' as being undefined. So I created the following variables:

Dim LastRow as Long
Dim LastCol as Long

I then received a missing 'Next' error message, so I added this loop to the end of the script.

Unfortunately when I ran this I received a 'Compile' error message.

I'm not sure where I've gone wrong with this. Could you perhaps tell me please have you any ideas?

Many thanks and kind regards

Chris

[SamT]

Chris, My bad. From your code example, I assumed you were more familiar with VBA

LastRow and LastCol have to be UDF's. For Examples:
[VBA]
Private Function LastRow() As Long
Const ColToCheck As Long = 1 'Col "A", column that has a value in the last used row
LastRow = ActiveSheet.Cells(Rows.Count, ColToCheck).End(xlUp).Row
End Function

Private Function LastCol() As Long
Const RowToCheck As Long = 7 'A Row that has a value in the last used Column
LastCol = ActiveSheet.Cells(RowToCheck, Columns.Count).End(xlToLeft).Column
End Function

[/VBA]

[hobbiton73]

Hi @SamT thank you for your continued help with this, and my sincere apologies for not getting back to you sooner.

I tried the code you kindly sent, but unfortunately I received the following error:

Run-time '1004' Application-defined or object-defined error'

I'm not sure where I'm going wrong here. I wondering whether I need to approach this from a different angle.

Many thanks and kind regards

[mdmackillop]

[VBA]Option Explicit

Sub Test()
Dim r As Range, cel As Range
Set r = Range("B7:B" & LastRow("B"))
For Each cel In r
With cel
If .Value > 0 And .Value <> "Enter your name" Then
.Offset(, 1).Resize(, 28).Locked = False
.Offset(, 1).Resize(, 28).Interior.ColorIndex = 6 'For test purpose
End If
End With
Next
End Sub

Private Function LastRow(Col) As Long
LastRow = ActiveSheet.Cells(Rows.Count, Col).End(xlUp).Row
End Function
[/VBA]

[hobbiton73]

Hi @mdmackillop, thank you very much for taking the time to reply to my post and for the solution, it works great.

All the best and kind regards