VBA Code - color changing cells

[greenfiore]

Hello!
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other". I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).

Any advice? Thank you!

 

[schielrn]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer, L As Boolean, B As Boolean
If Target.Address(False, False) = "E10" Then
    Select Case Target.Value
        Case "Other": iCol = 15: L = True: B = True
        Case Else: iCol = 2: L = False: B = False
    End Select
    With Range("E12")
        .Interior.ColorIndex = iCol
        If B Then 
          .Locked = L
             .ClearContents
        else
             .offset(2,0).clearcontents
          .offset(2,0).Locked = L
        end if
    End With
End If
End Sub

But lock only works when the sheet is protected.

If the sheet is not protected then lock is irrelevant.

Hope that helps.

 

[greenfiore]

I protected the worksheet but for some reason when I get to sell E10, a popup box comes up saying "Unable to set the colorindex property of the Interior class".

 

[schielrn]

Because the sheet is now protected and the code will not run.

At the beginning of the code put:

activesheet.protect "password goes here if you have one"

Then at the end put:

activesheet.unprotect "password goes here if you have one"

Hope that helps.

 

[greenfiore]

I guess I am not really understadning where this password/unprotect portion is supposed to go. No matter what drop down box/cell I click on it reverts me to the VBA window saying the password that I supplied is incorrect - however it didn't ask me for one. While I have been asking for help on cells E10-E14, there are a number of cells before hand that a user will have to click on beforehand. I would like the sheet to be protected by me, so that the user does not have access to change the formulas, etc.

How exactly is this possible?

 

[schielrn]

You will probably want to select all cells and then press control+1 to bring up the format cells dialog. Then go to the last tab and make sure that locked is unchecked.

Then all cells are unlocked. Then manually lock the cells that you do not want them to change. Then protect the sheet again.

Then try using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer, L As Boolean, B As Boolean
activesheet.protect "password goes here if you have one"
If Target.Address(False, False) = "E10" Then
    Select Case Target.Value
        Case "Other": iCol = 15: L = True: B = True
        Case Else: iCol = 2: L = False: B = False
    End Select
    With Range("E12")
        .Interior.ColorIndex = iCol
        If B Then 
          .Locked = L
             .ClearContents
        else
             .offset(2,0).clearcontents
          .offset(2,0).Locked = L
        end if
    End With
activesheet.unprotect "password goes here if you have one"
End If
End Sub

Hope that helps.

 

[VoG]

I think it should be the other way round:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer, L As Boolean, B As Boolean
Me.Unprotect "password goes here if you have one"
If Target.Address(False, False) = "E10" Then
    Select Case Target.Value
        Case "Other": iCol = 15: L = True: B = True
        Case Else: iCol = 2: L = False: B = False
    End Select
    With Range("E12")
        .Interior.ColorIndex = iCol
        If B Then
          .Locked = L
             .ClearContents
        Else
             .Offset(2, 0).ClearContents
          .Offset(2, 0).Locked = L
        End If
    End With
Me.Protect "password goes here if you have one"
End If
End Sub

 

[greenfiore]

Yeah I agree with switching the protect/unprotect in the code. It is now working without a prompt (great!), however it is not actually working by locking the cells. i.e. when cell E10 <> Other I can still type in cell E14. Will this only lock after the code is over?

 

[greenfiore]

And when does the code actually end and reprotect?

 

[greenfiore]

Also, how do I make cell H10 clear if E10 <> "Other"? Thanks!

 

[schielrn]

I think it should be the other way round:

oops sorry, that is what happens when I'm just trying to put something up in a rush.