Macro to Lock cells in protected sheet depending on criteria

Hi Guys,

I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

Thanks in advance for any help offered.

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

Sorry to be a pain but is there a possibility that this can be acheived?

Thanks.

Your code would need to Unprotect, work then re-protect unless you Protect with UserInterface. See the examples in the VBA section of www.excel-it.com. If you need help attach an example workbook.

Thanks for your help guys I appreciate your input.

@suhas.nehete

Thanks for attaching that sample spreadsheet I shall see if I can adapt that to my worksheet it looks like the sort of thing I am after.

Thanks again guys I shall let you know if this works or not.

I think your spreadsheet desdign is amking work for you. Why not have one column in which you enter Credit Note or Invoice then you can use AutoFilter or a PivotTable to see the individual totals for each item.

Hi Guys,

I tried your code suhas.nehete and it works great but I need it be dynamic to so that it will work in every row that has data.

@RoyUK

I hear what you are saying but this spreadsheet is just simply a record to check what credit notes and invoices that I have received the calculations are just simple I don't really need a Pivot Table as it was only ever meant to be a log really.

I have attached the spreadsheet, maybe if you have some spare time could you look at it for me and let me know if the code provided here can be modified to tend to my needs.

Basically in a nutshell this is what I need. if Column C in any row become populated then cells I & J in that row become locked, likewise if column I become populated then cells G & H in that same row are locked.

How can I acheive this and make the macro work automatically so that it locks the cell after data has been entered into it?

I ammended it as below but my vba skills are minimal.

Sub LockCells()
'
' Macro1 Macro
' Macro recorded 12/17/2007 by suhas nehete
'

'
Dim Rng As Range
Set Rng = Sheets("Inv Receipt").Range("G3:G1505")
Set Rng1 = Sheets("Inv Receipt").Range("I3:I1505")

Sheets("sheet1").Unprotect "open"

If (Cells(Rng) > 0) Then
Cells(7, 4).Locked = True
Else
Cells(7, 4).Locked = flase

End If

Sheets("sheet1").Protect "open"
End Sub