Excel: Copy cell colour from a named range to a drop down menu

Question

Can anyone help me? I have a named range in sheet1 and on sheet2 cells that reference the named range as a drop down list. I want to copy the cell colour from the named range sheet1 when it’s selected in the drop down list sheet2. I’d be grateful for any support.

Kresimir L. Kresimir L. · Accepted Answer · 2017-09-21T10:38:16

You can achieve this through VBA Worksheet_change event. Make sure that your colored range starts in cell A1 on sheet1. then in sheet2 enter this code in Worksheet module (not regular module).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
      x = Application.WorksheetFunction.Match(Range("A1").Value, Worksheets("Sheet1").Range("NamedRange"), 0)
      Range("A1").Interior.Color = Worksheets("Sheet1").Range("A" & x).DisplayFormat.Interior.Color
    End If
End Sub

This code assumes you have your data validation cell in in cell A1, but you can adjust Target.Adress and range("A1") part of code to your needs.

Excel: Copy cell colour from a named range to a drop down menu

1 Answers