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!

 

[VoG]

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

Well I've never done that (just kidding)

I think this should work - I've moved the Unprotect inside the first If

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
    Me.Unprotect "password goes here if you have one"
    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(0, 3).ClearContents
            .Offset(2, 0).Locked = L
        End If
    End With
    Me.Protect "password goes here if you have one"
End If
End Sub

 

[greenfiore]

Do I want to put the Offset(0,3).ClearCOntents somewhere else in the code? I am trying to clear out cell H10. Would'nt this clear out H12?

 

[VoG]

Oops!

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
    Me.Unprotect "password goes here if you have one"
    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, 3).ClearContents
            .Offset(2, 0).Locked = L
        End If
    End With
    Me.Protect "password goes here if you have one"
End If
End Sub

 

[greenfiore]

Shoot - I messed up. I have merged cells H10-J10. Is there anyway to do this? - ps this stuff is great thanks for your hlep!

 

[VoG]

Unmerge those cells, Format > Cells > Alignment tab > set Horizontal alignment to Center across selection.

 

[greenfiore]

Okay the last little hiccup - it looks like the Locked property isn't really doing anything. When the sheet is unprotected I have all of the cells Locked. Then I went through and individually unlocked all of the cells that I want the user to be able to adjust, including E10, E12, E14, H10. I then protect the sheet. When I go through the sheet it still allows me to select E12 and E14 no matter what the input in E10 is. Is there some formatting i need to do prior to protecting the sheet? Thanks!

 

[greenfiore]

Is there anyway to have a message box pop up when cell E10 is changed to say "Other"? Thanks!

 

[VoG]

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
    Me.Unprotect "password goes here if you have one"
    Select Case Target.Value
        Case "Other": iCol = 15: L = True: B = True: MsgBox "E10=Other"
        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, 3).ClearContents
            .Offset(2, 0).Locked = L
        End If
    End With
    Me.Protect "password goes here if you have one"
End If
End Sub

 

[greenfiore]

There was no way to alter that locking function right?