A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

vlookup question



 
 
Thread Tools Display Modes
  #1  
Old September 26th, 2003, 04:47 AM
jd
external usenet poster
 
Posts: n/a
Default vlookup question

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


  #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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.