Locking and formatting cell based on variable values of other cells

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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:
Code:
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.
 
Last edited:

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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

New Member
Joined
Nov 27, 2018
Messages
14
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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

New Member
Joined
Nov 27, 2018
Messages
14
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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

New Member
Joined
Nov 27, 2018
Messages
14
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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.
 

Forum statistics

Threads
1,089,333
Messages
5,407,642
Members
403,156
Latest member
Fahad Saleem

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top