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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|