VBA code to prevent typing in Cells and Columns

Hi,

Just wondering if anyone had, I am guessing 5 mins to help me out with something that is probably very obvious (but not to me - at the moment)

Essentially I have a template spreadsheet with months in columns and departments in rows. Rows per department will be consistent throughout but will vary between departments. I have written code that will prevent overtyping previous month / future month data, and also to ensure each department can only enter data for their department. Individually both sets of code run.

However, what I need to do is amend the code so that rows and columns will both be blocked except for those that intersect each other - and that is where I have broken it.

Just wondering if someone could take a peek at my attempt, and when you've finished laughing point me in the right direction.

Sub columnlock()


Dim mycell As Range
Dim strPassword As String
Dim x As String
Dim Value As Range




Sheet1.Select
ActiveSheet.Unprotect Password:="hello"
Sheet2.Visible = xlSheetHidden




Cells.Locked = False


For Each Value In Range("M2:AJ2").Cells
If Value <> "TT" Then
Value.EntireColumn.Locked = True
End If
Next


For Each Value In Range("A40:A120").Cells
If Value <> "TT" Then
Value.EntireRow.Locked = True
End If
Next 


Sheet2.Visible = xlSheetVeryHidden
ActiveSheet.Protect Password:="hello"

From the error messages I think it doesn't like the 2 lots of "for each"

Any help is appreciated.


Many thanks

Lynton

Hi there KjBox..... (and Everyone Else)

Firstly, that's a hell of a lot neater and tidier than I have done. As said I am kind of rusty (probably totally corroded) with VBA having had nearly 3 years away from it and now finding myself using it more and more, so some is coming back to me, (too slowly) but am refreshing and learning new every day.

I think I can understand as to what you have done (as in understand the code) ... I've copied the code in and run it however it is not doing what I intended. (However at least it's not falling over at the not liking 2 For statements any more. )

This is due to my rather poor description so have attached a screenshot of a mini mocked up spreadsheet to hopefully illustrate what I am trying to do.

I've added a few comments to the spreadsheet - just as text in cells.

Essentially what I am trying to achieve is if the value of a cell within the range M2:AB2 is "TT" AND if the value of a cell in A5:A200 is "TT" then lock all the cells in the range A5:AB200 except where the rows and columns intersect.

M2:AB2 will always only have one TT maximum as it relates directly to a month.
A5:A200 will always have the same number of TT values however this will vary depending upon the department selected.

Dept A will always be on rows 5-8
Dept B will always be on rows 10-22
Dept C will always be on rows 24-25
etc etc

Any pointers/help would be massively appreciated.

Many thanks