Vlookup with color

[A_K]

Hi,

I have two lists. The first lists has colored cells in front of some of the items on the list. Is there a way for excel to look up whether the cell in front of an item is colored in the first list, and if yes, to apply a [different] color to a cell next to the same item in the second list? please see the example screenshot.

Thank you for your help!
A_K

 

[mmmarks]

Yes you can do it but wit VBA..
Try below code in sample work book hope it will works

Sub Mn()

Dim rcnt As Long
rcnt = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To rcnt

 If Range("A" & i).Value <> "" And Range("B" & i).Interior.ColorIndex <> xlNone Then
    Range("F" & i).Interior.ColorIndex = 10 ' change color code
 End If
 
 Next i
 
 
End Sub

Thanks
A

 

[A_K]

Thank you, this worked when the order of the items in List 1 and List2 was the same.

But I have also tried changing the order (i.e., not a, b, c, d, but instead ordered randomly) in List2, in which case the colors were appearing not in front of the cells with corresponding values, but just were in front of the items in the same rows in List 2.