add color to drop-down list

[skunkworks]

Column J, which holds items to be included in a drop-down list, is typically hidden. When I select an item in the list in column C, I'd like it to turn green (or pink or whatever color I fancy that day), as shown in Column J. How can this be done?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Target.Interior.ColorIndex = Range("J2").Interior.ColorIndex
End Sub

 

[skunkworks]

Thanks, mumps! A new cell in column C is now gray. How can I change it to another color?

Also, using the code you supplied necessitated saving the file as macro-enabled. Is that correct?

 

[mumps]

Also, using the code you supplied necessitated saving the file as macro-enabled. Is that correct?

That is correct.

A new cell in column C is now gray. How can I change it to another color?

I'm not sure what you mean. Please clarify in detail referring to specific cells, rows and columns using a few examples from your data.

 

[skunkworks]

Please see attached image showing new gray cells in column C. I'd like to have the ability to change the color of all cells in column C to another color for each new production. In this case, I'd like them to be close to the green shown in column J.

 

[mumps]

You would have to click on each grey cell and re-select the value in the drop down list. Whenever you select a value from the drop down, the color will change to that in cell J2.

 

[skunkworks]

It's not that important. The original version of this file, received from someone else, had all cells in column C green. 'Tis a mystery.

Thanks again for your help. It's much appreciated!

 

[mumps]

You are very welcome.