Drop down list that will import value + background color from source and cell next to it

[Spidii]

Hi,

I tried to look for formula/script that would make something like that work but i wasnt able to find one. Probably the reason is that it is hard to name it correctly to search for it.

I want to make that drop down list on right side to automatically change background color based on selection. So at current moment it would need to have same background color as cell with "1" in it at that table at the left side. In addition when i pick something from drop down list i want it to use data from cell that is at the right side of source so in current moment it would be "11". Background color of that cell with 11 in it should be imported also.

If i would chose 4 from drop down list then cells at the right side should have blue background with 4 and 44 in them.

I want to be able to add/remove stuff to the list at the left side so i think that i cant use condition formatting because list changes pretty often and i dont want to update formatting every single time.

Data in 1st column will never double
Data in 2nd column might be same in all cells
There can be random amount of cells per each color.

If someone could at last tell me what i need to look for to make it work then it would be super cool!

Thanks!

 

[jasonb75]

I would start with something like this (most basic form of the code) then expand it as and if needed.

Note that F3 refers to the cell with the dropdown and B3:B20 refers to the range of values in the first column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "F3" Then Range("B3:B20").Find(Target.Value, , xlValues, xlWhole, , , False, False, False).Offset(, 1).Copy Target.Offset(, 1)
End Sub