Transferring color with the drop down list value

[22strider]

Hello Friends

I am using Excel 2007. In my spreadsheet I have created a drop down list for entering values in cells. The master list (from where the drop down list is reading values) has color formats. Is it possible to format cells of the spreadsheet same as the master list?

For example if I select country Canada from the drop down list and in the master list cell containing Canada has fill color Red, is it possible that the same color Red gets transferred to the sheet where I am making the entry?

Thanks
Rajesh

 

[JoeMo]

Hello Friends

I am using Excel 2007. In my spreadsheet I have created a drop down list for entering values in cells. The master list (from where the drop down list is reading values) has color formats. Is it possible to format cells of the spreadsheet same as the master list?

For example if I select country Canada from the drop down list and in the master list cell containing Canada has fill color Red, is it possible that the same color Red gets transferred to the sheet where I am making the entry?

Thanks
Rajesh

Assuming the drop down is from validation and includes the range A1:A10 (change code to suit) and that the list feeding the drop down is in J1:J6 (change code to suit), this worksheet_change event module will do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rList As Range, n As Long, myColor As Long
'Change next line to range with drop down
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Set rList = Range("J1:J6") '<-- change range to suit
    n = Application.Match(Target.Value, rList, 0)
    If n > 0 Then
        myColor = rList.Cells(n).Interior.Color
        Target.Interior.Color = myColor
    End If
End If
End Sub

 

[22strider]

Hello JoeMo

Thank you very much. Code does what I wanted to do.

Rajesh