VBA Coding Using VLookup

[Antney]

Hi,

I'm trying to color in a cell based on a vlookup. Can anyone tell me how to
do this?
The VBA coding seems to work fine, if I type in the color name but I can't
type in the name, I need the vlookup to populate the cell with the color name
and then I need the VBA coding to color the cell based on that color name.

Any help would be appreciated.

Thank you.

 

[Souny]

Antney,

Have you tried Conditional Formatting from the Format menu? In the
Conditional Formatting, you can say if red, color the cell with red.

I hope that helps.

 

[Patrick Molloy]

yuo don't give much information, but lets suppose that you have a table,
range named colorTable where the color name is in the first column and the
excel color is in column 2- the cell in 2 is colored not named.this gives you
flexibility

I'll also assume that your target cell is data validated using the list from
the first column of colorTable, the column I have named 'colors' ....this is
used in my check...

to achieve your goal, we'll use the CHANGE event fired when th evalue of the
target cell changes.
You will need to have some "rule" such as this will affect cells in a
certain range or column. My code is this illustrative ....
opne the sheet's code page by right-clicking the tab and selecting view code
from the pop-up menu, and paste this code


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo quit
If Target.Validation.Formula1 = "=colors" Then
SetColor Target
End If
quit:
End Sub

Sub SetColor(Target As Range)
Dim index As Long
On Error Resume Next 'trap if there's no validation
index = WorksheetFunction.Match(Target.Value, Range("colors"), False)
If index > 0 Then ' trap if there's no match
Target.Interior.Color = Range("tableColor").Resize(1, 1).Cells(index,
2).Interior.Color
End If