Change Interior Color from VLOOKUP Cell?

[fstormby]

Hi,

Got the below piece of code in my worksheet "POSITION SETUP". In that sheet i have two columns in a table, A where i fill in my position title, ex. "POS1", and B where i select the words "NONE","RED","YELLOW","BLUE"....Etc. from a validation list.
I use the attached code for that worksheet and when i for example choose "RED" in B2, B2 goes red. So far so good.

Then i want to use the same code on the worksheet "EQUIPMENT SETUP" where i have a table with the columns "COLOR", "POSITION" and 5 more.
In column POSITION i have a validation list based on the data from "Sheet POSITION SETUP:Column A"
In column "COLOR" i use VLOOKUP to insert the color from Sheet "POSITION SETUP"

So i get:
COLOR POSITION
Red Pos1

Since, what i understand the Workbook_Change event do not fires on formula i need the code to activate on my dropdown selection in POSITION Column, but change the color of the cell with "Red" in....

I've been trying this back and forward with different codes and i just cant get i right...
Attached code is set for the Sheet that i got working now.

Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "C:C"

On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target
Select Case .Value
Case Is = "None": .Interior.ColorIndex = 1 'black
Case Is = "Red": .Interior.ColorIndex = 3 'red
Case Is = "Yellow": .Interior.ColorIndex = 6 'yellow
Case Is = "Blue": .Interior.ColorIndex = 8 'blue
Case Is = "Purple": .Interior.ColorIndex = 7 'lav
Case Is = "Green": .Interior.ColorIndex = 4 'green
Case Is = "Orange": .Interior.ColorIndex = 46 'orange
Case Is = "White: .Interior.ColorIndex = 0 'white"
Case Is = "Brown": .Interior.ColorIndex = 53 'brown
Case Is = "Grey": .Interior.ColorIndex = 15 'grey
End Select
End With
End If

endit:
Application.EnableEvents = True
End Sub

Regards,
Fredrik

 

[Rijnsent]

Hej Fredrik,
I think I kind of understand your problem, so you basically want to change the background color of a cell you're not in, but is in the column next to it?
What about adding to you code something like IF activeworksheet.name = "EQUIPMENT SETUP" THEN Target = Target.offset(0,-1)?
Hope that gets you under way?
Cheers,
Koen