Locking and formatting cell based on variable values of other cells

[PRADEEPSWALSE]

Hi,
I need help in excel for locking and formatting cell based on variable values of other cells

If A1 cell is having value of “CL3” or “GP1” or “GP2“ or “GP3“ or “GP4“ or “SB1“ or “SB2“ or “SB3“ or “SF1“ or “SF2“ or “SF3“ or “SP1“ or “SP2“ or “TB1“ or “TP1“ or “TP2“ or “TP3” etc. then B1 cell should be locked and same to be filled with red colour and if value other than above then B1 cell to be unlocked for other data input.

Same is applicable to A2, A3, A4…… and B2, B3, B4….. approx.. 500 rows.


Regards,

Pradeep S. Walse

 

[mumps]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

Make sure that the cells in columns A and B are unlocked and the sheet is protected. The try this macro:

Sub LockCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Dim LastRow As Long, rng As Range, arr As Variant, i As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    arr = Array("CL", "GP", "SB", "SF", "SP", "TB", "TP")
    For Each rng In Range("A1:A" & LastRow)
        For i = LBound(arr) To UBound(arr)
            Select Case True
                Case rng.Value Like arr(i) & "*"
                    With rng.Offset(, 1)
                        .Locked = True
                        .Interior.ColorIndex = 3
                    End With
                    Exit For
                Case Else
                    With rng.Offset(, 1)
                        .Locked = False
                        .Interior.ColorIndex = xlNone
                    End With
            End Select
        Next i
    Next rng
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

You can add variables to the array to suit your needs.

 

[PRADEEPSWALSE]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

Hi,

Thanks for the solution but it is not working at my end. May be I missed something. I am new to VBA.

Refer attached ‘Sample Excel File_123.xlsx’ for your ready reference.
I have unlocked column A & B and also sheet is protected without any password.
Then I right click on sheet and selected ‘View Code’. I have copy-paste code provided by you and simultaneously pressed ALT+Q.

Now I have entered ‘CL’ and ‘GP’ in A1 and A2 respectively. But B1 and B2 is not locked with RED filled colour.

Can you please attached file and guide where I had made mistake.

(I am not able to attached my sample excel file. How to attach file here)

Regards,

Pradeep S. Walse

 

[mumps]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

This forum does not allow file attachments. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets.

 

[PRADEEPSWALSE]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

Hi,

Please download sample file from this link https://we.tl/t-dPMqmsd63M.

Something is missing from my end. What I have done is mentioned in my earlier post.

My requirement is :

If A1 cell is having value of “CL3” or “GP1” or “GP2“ or “GP3“ or “GP4“ or “SB1“ or “SB2“ or “SB3“ or “SF1“ or “SF2“ or “SF3“ or “SP1“ or “SP2“ or “TB1“ or “TP1“ or “TP2“ or “TP3” etc. then B1 cell should be locked and same to be filled with red colour and if value other than above then B1 cell to be unlocked for other data input.
Same is applicable to A2, A3, A4…… and B2, B3, B4…..

 

[mumps]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

It worked for me. Click here to download your file.

 

[PRADEEPSWALSE]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

Can you please send excel file with empty cell of A1 & A2?
So that I can enter 'CL' in A1 cell and will observe the result at my end i.e. B1 is locked with red color filled.

 

[mumps]

Re: I need help in excel for locking and formatting cell based on variable values of other cells

You can simply unprotect the sheet and clear A1 and A2 and the color in column B or simply start with a new blank sheet.

 

[PRADEEPSWALSE]

Hi,
I tried by clearing A1, A2 but not working at my end.

I am using MS office 2013. I am not aware VBA.
I have downloaded file (Sample Excel File_123_1.xlsm) sent by you and saved in Desktop and opened in MS Excel by double click from desktop.

Some thing basic is missing at my end.

 

[mumps]

In the file that I attached, you will notice that cells B1 and B2 are locked and coloured red and the sheet is protected. The macro works as you requested. Are you using the macro on a different file? If you are, please upload a copy of the actual file that is not working for you and explain in detail how it is not working.