Vlookup with colour possible?

[Guest]

Simple question I think but I can't figure it out.

How do I add colour backgrounds to certain vlookup product cell data
(different colours for chosen data) when the combobox is clicked for a list a
products sold, the colour shows up for that product that is retrieved from
the vlookup?

Any idea?? or not possible?

 

[Gord Dibben]

You could use Conditional Formatting but sounds like you may have too many
conditions for one cell.

You may have to go with sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Thanks for the reply but I should have given an example as i do not know how
to use this code you supplied exactly in vlookup with other code in the
editor for comboxes.

eg. The data chosen from using comboboxes.
X Y
1 Fish9 $50 shows the product in blue background/text
2 Fish4 $40
3 Fish2 $50
4 Fish9 $50
5 Fish2 $50
6 Fish1 $20
etc

If Fish1 is pink, Fish2 is orange, Fish9 is blue... how would you code these
cells to show the background or text colour?
Would I choose in the lookup table the data to be coloured manually of
course then in the sales sheet use the combo boxes to select the fish product
and will show the colour?

Cheers.

 

[Guest]

Thanks for the reply but I should have given an example as i do not know how
to use this code you supplied exactly in vlookup with other code in the
editor for comboxes.

eg. The data chosen from using comboboxes.
X Y
1 Fish9 $50 shows the product in blue background/text
2 Fish4 $40
3 Fish2 $50
4 Fish9 $50
5 Fish2 $50
6 Fish1 $20
etc

If Fish1 is pink, Fish2 is orange, Fish9 is blue... how would you code these
cells to show the background or text colour?
Would I choose in the lookup table the data to be coloured manually of
course then in the sales sheet use the combo boxes to select the fish product
and will show the colour?

Cheers.

 

[Gord Dibben]

I'm having a hard time getting around this one.

You use the word "if"

What color would Fish2 and Fish9 be if Fish1 is not pink?


Gord