VBA to colour code cellls based on specific cell value but i cant get to "Lock" / Unlock based on cell value.

[joelmathew01]

Hello thanks for reading query.
I work in Insurance and spend some time designing spreadsheet for others to use.

query i have - if Cell O2 = " Local" - i want v2, w2, y2, etc to be "white" coloured and locked . so ppl cant use.
if it says " International" - then i want the above mentioned cells to be "yellow" but no locked.
This should run down each rows of the spreadsheet. I can get the highlighting to work but the cell locking is throwing errors.

The below macro works in terms of highlighting the the rows but i cant lock or unlock the cells .

if " Local" is selected - then i want

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim i As Long, r1 As Range, r2 As Range

   For i = 9 To 32
      Set r1 = Range("o" & i)
      Set r10 = Range("AG" & 1)
      Set r2 = Range("V" & i, "aa" & i)
      Set r3 = Range("AE" & i, "ag" & i)
      Set r4 = Range("ai" & i)
      
       If r1.Value = "Local" Then r2.Interior.ColorIndex = 2
       If r1.Value = "Local" Then r3.Interior.ColorIndex = 2
       If r1.Value = "Local" Then r4.Interior.ColorIndex = 2
       
    If r1.Value = "International" Then r2.Interior.ColorIndex = 19
    If r1.Value = "International" Then r3.Interior.ColorIndex = 19
    If r1.Value = "International" Then r4.Interior.ColorIndex = 19
 
If r1.Value = "" Then r2.Interior.ColorIndex = 19
    If r1.Value = "" Then r3.Interior.ColorIndex = 19
    If r1.Value = "" Then r4.Interior.ColorIndex = 19

  Next i
End Sub

 

[vletm]

joelmathew01
Why do You would like to LOCK O-column cells?
or is it matter are those locked or not ... because those are formulas ( if M-column has "Australia" then "Local" ).
M-columns data validation seems to have some challenges ... no matter now! ... there can always write.
Of course, it could un/lock if needed, but in many cases then sheet should protect that un/locking works or other solution.
What really would You like to have?

 

[joelmathew01]

when any row in "O " is selected " Local" , then I would like ranges r2, r3 and r4 to be locked. abiously the sheet will be protected.
basically is its Local in column O in any row, then the ranges need to be white and locked.
if these change to "International", then they should be yellow out ( this is working) but unprotected and should be able to enter in the ranges.
I hope I am making sense. my macro was written in away so I can use it for multiple rows but the lock unlock is not working.

 

[vletm]

joelmathew01
As I tried to write:
#1 There is NO any selecting with O-column ... result of formula from M-column!
#2 Is something should be something eg protected then sample-file should also be protected.
#3 Un/locking cannot work before there are any actions of un/locking ... that's why those couldn't work.
#4 Why need to do something for 23 rows? or how do You 'select' something?

 

[Marc L]

Hi !​

when any row in "O " is selected " Local"

Nothing is "selected" : it's a cell formula !​

As the worksheet Change event does not work with any formula …​

​

 

[vletm]

Marc L
OP maybe thinks something ...
but someway Worksheet_Change ... works ... but not with O-column - 'change'!
... as I also has tried to explain.

 

[joelmathew01]

sorry- if Australia is selected in cell Column M" , that will automatically say " Local" . any other country would be international.
I I think I screwed up the file when I uploaded it. Please see updated file.

 

[joelmathew01]

I know a vba to lock and unlock cells via vba.. its trying to incorporate both these together.

 

[vletm]

joelmathew01
Your 'new file' looks to have same feature ... nothing has changed!

Would You try to sit down for few seconds ... maybe minutes
#1 read others replies
#2 think
#3 read others replies again
#4 think again
#5 after those, try to write something new here ... please

 

[Marc L]

In next code, I do not incorporate worksheet protect / unprotect​

as the worksheet protection explanation (how do you ?) is missing …​

​

As a beginner starter according to post #7 :​

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim C%
    If Target.Column = 13 And Target.Row > 8 Then
             If Target.Value2 = "Australia" Then C = 2 Else C = 19
        With Union(Cells(Target.Row, "V"), Cells(Target.Row, "AE"), Cells(Target.Row, "AI"))
            .Interior.ColorIndex = C
            .Locked = C = 2
        End With
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​