Validation list keeps original formatting

[BYUIStudent]

Hello Mr. Excel community and thank you for your help! You all are always teaching me new things thank you.

I have a validation drop down list on sheet 2, with the original list on sheet 1. The list on sheet 1 has a specific fill color based on the name of the item.

How can I make it so that the color coded name shows up when that name is selected in the validation box.

I've looked around and found this topic www.mrexcel.com/forum/excel-questions/51589-dropdown-list-values-how-maintain-original-format.html

Which seems to be what I'm looking for except that it's different sheets. I've also never done event coding so I'm struggling with understanding the code.

Looking forward to resources to help me learn. Once again thank you!

BYUIGrad.

 

[Fishboy]

Hello Mr. Excel community and thank you for your help! You all are always teaching me new things thank you.

I have a validation drop down list on sheet 2, with the original list on sheet 1. The list on sheet 1 has a specific fill color based on the name of the item.

How can I make it so that the color coded name shows up when that name is selected in the validation box.

I've looked around and found this topic www.mrexcel.com/forum/excel-questions/51589-dropdown-list-values-how-maintain-original-format.html

Which seems to be what I'm looking for except that it's different sheets. I've also never done event coding so I'm struggling with understanding the code.

Looking forward to resources to help me learn. Once again thank you!

BYUIGrad.

Hi BYUIGrad,

One of of doing this would be to hardcode the colours via your VBA code. This is probably only worthwhile if your drop-down lists do not contain too many possible selections (deciding how many is too many is up to you rather than a technical limitation).

The following worksheet_change code is applied directly to the backend of your data validation list sheet. To do so make a COPY of your workbook to test on. Next you simply right-click on the sheet name, select View Code and copy / paste in the following code. You would also need to save your document as a macro enabled workbook (.xlsm format)

Private Sub Worksheet_Change(ByVal Target As Range)
' If target column is A and target value is not blank then...
    If Target.Column = 1 And Target.Value <> "" Then
' If target value is red then colour the cell red
        If Target.Value = "Red" Then
            Target.Interior.ColorIndex = 3
' Else if target value is blue then colour the cell blue
        ElseIf Target.Value = "Blue" Then
            Target.Interior.ColorIndex = 5
' Else if target value is green then colour the cell green
        ElseIf Target.Value = "Green" Then
            Target.Interior.ColorIndex = 4
' Else if target value is yellow then colour the cell yellow
        ElseIf Target.Value = "Yellow" Then
            Target.Interior.ColorIndex = 6
        End If
' Else if the target value is blank then clear the cell colour
    ElseIf Target.Column = 1 And Target.Value = "" Then
       Target.Interior.ColorIndex = xlNone
    End If
'' Link to VBA colour index - http://dmcritchie.mvps.org/excel/colors.htm
End Sub

In my example code above I have only accounted for 4 different drop-down options but you could expand this as much as required, and this is where you have to decide how much is too much to be worth doing it. You will obviously also need to update the target.values in my example code from "Red", "Blue", "Green" and "Yellow" to suit your data. Next look at the link I provided at the end of the code to work out what colorindex relates to what colour. The number you are interested in is the one from the second column (font).

Whilst this may not be the most ideal solution, it is fairly simple to implement, just potentially a little time consuming to set up depending on how many options you need to account for.