Lock Certain Cells that Meet Certain Certeria
 

Hello,

I'm trying to use FOR - NEXT to cycle through the Range B:B or even
better go to the last row (which I don't know how to do successfully -
it always stops short a few rows). I would like to find each cell that
has an "X" and then lock that row or if no "X" leave it unlock.

I have the entire worksheet unlock - so when this runs the only locked
rows are the ones with an "X" in Column B.

Here is my code so far, it locks cell A1 and that's it:

Sub LockCells()

Dim test1 As Range

Set test1 = Range("B2, B150")

For Each test1 In Selection
If test1 = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True

End If

Next

End Sub


Many Thanks!!
Dave

Lock Certain Cells that Meet Certain Certeria
 

Assuming each cell in the range contains data to the end of the list:

Sub LockCells()

Dim rng As Range

Set rng = Range("B2, B150")
Range("B2").Activate
For x = 1 To 200
If ActiveCell = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Exit For
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True
ActiveCell.Offset(1, 0).Activate
End If

Next

End Sub

Regards,

Jim

" wrote:

Hello,

I'm trying to use FOR - NEXT to cycle through the Range B:B or even
better go to the last row (which I don't know how to do successfully -
it always stops short a few rows). I would like to find each cell that
has an "X" and then lock that row or if no "X" leave it unlock.

I have the entire worksheet unlock - so when this runs the only locked
rows are the ones with an "X" in Column B.

Here is my code so far, it locks cell A1 and that's it:

Sub LockCells()

Dim test1 As Range

Set test1 = Range("B2, B150")

For Each test1 In Selection
If test1 = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True

End If

Next

End Sub


Many Thanks!!
Dave

Lock Certain Cells that Meet Certain Certeria
 

This only locked cell B2 now instead of just A1.

Dave

Jim Jackson wrote:
Assuming each cell in the range contains data to the end of the list:

Sub LockCells()

Dim rng As Range

Set rng = Range("B2, B150")
Range("B2").Activate
For x = 1 To 200
If ActiveCell = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Exit For
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True
ActiveCell.Offset(1, 0).Activate
End If

Next

End Sub

Regards,

Jim

" wrote:

Hello,

I'm trying to use FOR - NEXT to cycle through the Range B:B or even
better go to the last row (which I don't know how to do successfully -
it always stops short a few rows). I would like to find each cell that
has an "X" and then lock that row or if no "X" leave it unlock.

I have the entire worksheet unlock - so when this runs the only locked
rows are the ones with an "X" in Column B.

Here is my code so far, it locks cell A1 and that's it:

Sub LockCells()

Dim test1 As Range

Set test1 = Range("B2, B150")

For Each test1 In Selection
If test1 = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True

End If

Next

End Sub


Many Thanks!!
Dave

Lock Certain Cells that Meet Certain Certeria
 

How about this approach?

Columns("A:B").Select
Selection.AutoFilter Field:=2, Criteria1:="0"
'Cells.Select
With Selection

Selection.Locked = True
Selection.FormulaHidden = True
End With

Regards,

Jim

" wrote:

This only locked cell B2 now instead of just A1.

Dave

Jim Jackson wrote:
Assuming each cell in the range contains data to the end of the list:

Sub LockCells()

Dim rng As Range

Set rng = Range("B2, B150")
Range("B2").Activate
For x = 1 To 200
If ActiveCell = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Exit For
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True
ActiveCell.Offset(1, 0).Activate
End If

Next

End Sub

Regards,

Jim

" wrote:

Hello,

I'm trying to use FOR - NEXT to cycle through the Range B:B or even
better go to the last row (which I don't know how to do successfully -
it always stops short a few rows). I would like to find each cell that
has an "X" and then lock that row or if no "X" leave it unlock.

I have the entire worksheet unlock - so when this runs the only locked
rows are the ones with an "X" in Column B.

Here is my code so far, it locks cell A1 and that's it:

Sub LockCells()

Dim test1 As Range

Set test1 = Range("B2, B150")

For Each test1 In Selection
If test1 = "" Then
ActiveCell.Locked = False
ActiveCell.FormulaHidden = False
Else
ActiveCell.Locked = True
ActiveCell.FormulaHidden = True

End If

Next

End Sub


Many Thanks!!
Dave