UDF Vlookup to return cell formatting?

[Factotum]

I have a UDF which is essentially a vlookup function except that it also returns any cell comments in addition to the cell values (see code below). I got this code from this forum, but I don't remember who to give the credit to (sorry).

Can anyone think of a way to modify this function to also return the cell formatting, specifically the color of the cell? I thought I was done with this project two months ago, but a bug just emerged that could quickly be squashed if I could just modify this function. Thanks for any ideas!

Option Explicit
Function VlookupComment(LookupValue As Variant, LookupTable As Range, ColumnNumber As Long, MatchType As Boolean) As Variant


'This is essentially a VLOOKUP function except that it returns cell contents AND cell Comments
'Formula to be typed into cell of spreadsheet is identical to a regular VLOOKUP: =VlookupComment(lookup_value,table_array,col_index_num,false)


    Application.Volatile True
    Dim res As Variant
    Dim myLookupCell As Range
    res = Application.Match(LookupValue, LookupTable.Columns(1), MatchType)
    
    If IsError(res) Then
        VlookupComment = "Not Found"
    Else
        Set myLookupCell = LookupTable.Columns(ColumnNumber).Cells(1)(res)
        VlookupComment = myLookupCell.Value
        With Application.Caller
            If .Comment Is Nothing Then
            Else
                .Comment.Delete
            End If
            If myLookupCell.Comment Is Nothing Then
            Else
                .AddComment Text:=myLookupCell.Comment.Text
            End If
        End With
    End If


End Function

 

[Jerry Sullivan]

UDF's can't directly change cell formatting and for the most part, they are limited to returning a value to the cell holding the UDF. The ability to modify comments is a known exception which is why that UDF works for comments.

Post #18 of this thread shows a workaround that uses the Worksheet_Calculate event.
http://www.mrexcel.com/forum/excel-...vlookup-keep-cell-formatting.html#post3644436

 

[Factotum]

Thank you very much for your thoughts. I actually saw that post yesterday, but dismissed it for two reasons:
1. It seems to be quite a bit more advanced than me and I didn't have the time to really dissect it.
2. I'm attempting to fix a bug in a rather massive macro that I was hoping I wouldn't have to further complicate (especially since I'm supposed to train a colleague on how it works and he has no VBA experience).
I will, however, add this to my little book of useful macros in case it comes up again.

In the meantime, I was able to come up with a work-around. Just in case anyone finds this useful (although I'm sure it's far from the most efficient way to get the job done):

Dim c As Range

For each c in range("......")
     If c.Interior.Color<>16777215 Then
         c.value=1
     End If
Next

The 1 acts as a placeholder in any cell containing a fill color (other than white) and the vlookup pulls the 1 into the correct column. Since, in my project, I only want the cell color and not a cell value, I filter the column containing the vlookup results to show only cells with a value of 1. Once it has been filtered, I clear the contents and then use SpecialCells(xlCellTypeVisible) to add color "manually" to the cells.

Jerry - once again, thank you for your time spent helping me with this!