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]

Hello and welcome to MrExcel.

Try this: right click the sheet tab, select View Code and paste in

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

 

[greenfiore]

Great! That worked in terms of the colors so thank you. However, I'm not sure what the locked portion (the last line) of the code was meant to do because I'm not sure if it actually did anything...also, is there anyway to get cell E12 to automatically say "Other" when "Other" is chosen in in cell E10, or to clear the input that might already be in it?
Thanks again!

 

[VoG]

Locking/Unlocking will only have a tangible effect if the sheet is protected. 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 .ClearContents
        .Locked = L
    End With
End If
End Sub

 

[greenfiore]

Awesome. Thanks!

 

[greenfiore]

In the vba request I had earlier, I wanted to clear cell E12 if cell E10 said "Other". Can I add a code to clear cell E14 if cell E10 <> "Other"? Thanks!

 

[schielrn]

Maybe 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 
             .ClearContents
        else
             .offset(2,0).clearcontents
        end if
        .Locked = L
    End With
End If
End Sub

Hope that helps.

 

[greenfiore]

When I copy that in and try to run the macro in excel, it automatically opens the VBA window again and states "End with without with" and highlights the last End With portion of the code.

Ps. I deleted the .locked part of the code becuase I didn't need that anymore. Not sure if that changes anything...

 

[schielrn]

I forgot the end if and edited it, try the code now that I have made that change.

 

[greenfiore]

Oh yeah that worked- thanks! Also, the locked part of that code - what is it exactly supposed to do. Essentially, if "Other" is chosen in cell E10, it would be great to have cell E12 lock and cell E14 unlocked. Otherwise, if anything else was chosen in cell E10, then cell E12 needs to be unlocked and cell E14 locked.

suggestions?