VBA Code - color changing cells

greenfiore

Board Regular
Joined
Apr 29, 2009
Messages
60
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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

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

Code:
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

Board Regular
Joined
Apr 29, 2009
Messages
60
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Locking/Unlocking will only have a tangible effect if the sheet is protected. Try

Code:
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

Board Regular
Joined
Apr 29, 2009
Messages
60

ADVERTISEMENT

Awesome. Thanks!
 

greenfiore

Board Regular
Joined
Apr 29, 2009
Messages
60
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

Maybe try:

Rich (BB code):
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

Board Regular
Joined
Apr 29, 2009
Messages
60
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
I forgot the end if and edited it, try the code now that I have made that change.
 

greenfiore

Board Regular
Joined
Apr 29, 2009
Messages
60
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top