• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unlock cells based on the other cells - Read a lot of threads but need help

A

ameenhimaaya

New Member
I want to unlock and change color of the cell D6 if cell F2 on other sheet is "C", like this it should work between D7 and F3, D8 and F4

I am using on a decoder, so in a case where C appears on F2 I want an additional input on D6.

Please help me, I am so new to vba. But in an urgent need of this thing
 
Place it in sheet module of sheet2 where C will be checked in F2 & so on...

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Not Intersect([F2:F10], Target) Is Nothing Then
    If Target.Value = "C" Then
        With Sheet1
            .Unprotect 11
                .UsedRange.Cells.Locked = True
                    .Cells(Target.Row + 4, Target.Column - 2).Locked = False 'D6
            .Protect 11
        End With
    End If
End If
Application.ScreenUpdating = True

End Sub
 
For color change add a extra line

Code: 
.Cells(Target.Row + 4, Target.Column - 2).Interior.Color = vbYellow
 
Thanks for your help Deepak,
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Not Intersect([F2:F167], Target) Is Nothing Then
    If Target.Value = "C" Then
        With Sheet1
            .Unprotect "password"
                .UsedRange.Cells.Locked = True
                    .Cells(Target.Row + 4, Target.Column - 2).Locked = False 'D6
                    .Cells(Target.Row + 4, Target.Column - 2).Interior.Color = vbYellow

           .Protect "password"
        End With
    End If
End If
Application.ScreenUpdating = True

End Sub

worked, but now the problem is, all other cells other than the D6 which is being unlocked by the code is getting locked. I want the original protection status to be retained. Only D6 (and so on) need to be changed.

Another thing is, the value on F2 is being generated by a formula
=RIGHT(SUBSTITUTE(E4,"-",""),1)
When I enter manually into F2 the code works, but using this formula it failed, so can you make the code work as me needs please?

Thanks in advance
 
worked, but now the problem is, all other cells other than the D6 which is being unlocked by the code is getting locked. I want the original protection status to be retained. Only D6 (and so on) need to be changed.
Click to expand...

Just remove the below line

Code: 
.UsedRange.Cells.Locked = True

When I enter manually into F2 the code works, but using this formula it failed
Click to expand...

It should be triggered with Worksheet_Calculate

Code: 
Private Sub Worksheet_Calculate()
Dim r As Range

Application.ScreenUpdating = False
With Sheet1
.Unprotect "password"
    For Each r In [F2:F167]
        If r.Value = "C" Then
            .Range(r.Address).Offset(4, -2).Locked = False
            .Range(r.Address).Offset(4, -2).Interior.Color = vbYellow
        End If
    Next
.Protect "password"
End With
Application.ScreenUpdating = True

End Sub
 
Uploading the file Deepak, it may look ugly, the code is in PROCESSING sheet (F2 is from this is used to determine lock status), and Master is my Sheet1 where D6 output to be locked or unlocked.

Once again, my usage as a basic user of Excel may have made my project bit ugly hope you can help me.
 
Any tag id with C before - like TIC, PIC will generate C in F2 of PROCESSING sheet, and that should unlock the D6 in Sheet1 else if the tag id like PI or TI then it should not unlock the corresponding cell in sheet1

Hope my need is clear
 
error causing due to error

Check this..

Code: 
Private Sub Worksheet_Calculate()
Dim r As Range

Application.ScreenUpdating = False
With Sheet1
.Unprotect "password"
    For Each r In [F1:F167]
        If IsError(r) Then GoTo n
        If r.Value = "C" Then
            .Range(r.Address).Offset(4, -2).Locked = False
            .Range(r.Address).Offset(4, -2).Interior.Color = vbYellow
        End If
n:    Next
.Protect "password"
End With
Application.ScreenUpdating = True

End Sub
 
Hello Deepak, can you do one more small edit? That is if Tag ID on F2 changed from TIC to TIT (I mean the last letter is not C) then D6 should be cleared (If it contains data and a colour change should occur say to orange and that should get locked again )
 
Else the problem is that if a Tag id is given as TIC first and then changed to TIT then the additional column will remain unlocked that may create a problem
 
You must log in or register to reply here.
Back
Top