Cell locking macro not working

[bbennett77]

Hi I was looking to have as part of a larger macro (which is activated by a shortcut key) a piece of code to lock data filled cells in a certain range. The rest of the workbook gets locked at the end of the sub which copies and pastes bits and pieces of data from different sheets as new data is pulled in each week. This certain range of cells I need to keep unlocked as new data needs to be entered (1 row each week), but needs to be locked once the data is entered. I pulled this piece of macro from another post on here.. can't remember which one now, which was said to do what I was looking for.



Sheets("weekly observations").Select
Dim MyRange As Range
Set MyRange = Intersect(Range("c4:e53"), Target)
If Not MyRange Is Nothing Then
MyRange.Locked = True
Sheets("Weekly observations").Protect Password:="comvita"
End If



When I run the macro it gives me an error on this line saying there is no object


Set MyRange = Intersect(Range("c4:e53"), Target)

I am still learning macros, can someone please let me know why this isn't working?
TIA

[offthelip]

The intersect function works out where two range intersect ( not surprising given the name) the two range you specify are C4:e55 which is fine. the second is "Target" which is undefined because you there is no declaration of this above. I guess you copied this from a "worksheet_change" event subroutine which automatically defines "target" as a range

change it to:


Dim MyRange As Range
Set MyRange = Range("c4:e53")