View Single Post
  #2  
Old September 26th, 2003, 06:40 AM
J.E. McGimpsey
external usenet poster
 
Posts: n/a
Default vlookup question

Not with a built-in formula. You could return the fill color using a
User Defined Function (UDF), but you couldn't use it to color the
calling cell:

Public Function VLookupColor(Lookup_value As Variant, _
table_array As Range, col_indexnum As Integer, _
Optional range_lookup As Integer = 1) As Variant
Dim found As Variant
On Error Resume Next
found = Application.Match(Lookup_value, _
table_array.Resize(, 1), range_lookup)
If IsError(found) Then
VLookupColor = CVErr(xlErrNA)
Else
VLookupColor = table_array( _
found, col_indexnum).Interior.ColorIndex
End If
On Error GoTo 0
End Function

(note: there's no error checking other than for a non-match).

If you wanted to color the cell, you'd have to use an event macro.
Assume A1 is your lookup value, J1:K100 is your table and you want
an exact match:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim found As Variant
If Not Intersect(Range("A1,J1:J100"), Target) Is Nothing Then
On Error Resume Next
found = Application.Match( _
Range("A1").Value, Range("J1:J100"), 0)
If IsError(found) Then
Range("B1").Interior.ColorIndex = xlColorIndexNone
Else
Range("B1").Interior.ColorIndex = _
Range("J1")(found, 2).Interior.ColorIndex
End If
End If
End Sub


Put this in the worksheet code module (right-click on the worksheet
tab, select View Code, and paste into the window that opens):


In article ,
"jd" -move-th-is wrote:

Does anybody know if it is possible to not only return the value of a target
cell with the vlookup function, but an attribute of that cell too (in my
case, fill color)?

Thanks,
Jeff