Multivlookup

[tmaslauskas]

Hi all, I'm new to this forum but hope you can help me out!

I have created a calendar in Excel that works by using a 'multivlookup' function that I have defined as;

Public Function MultiVLookup(MatchWith As String, TRange As Range, col_index_num As Integer)
MatchWith = LCase$(MatchWith)
If (MatchWith = "") Then
MultiVLookup = ""
Else
For Each cell In TRange
If LCase$(cell.Value) = MatchWith Then
x = x & cell.Offset(0, col_index_num).Value & ";" & vbLf
End If
Next cell
If (x = "") Then
MultiVLookup = ""
Else
MultiVLookup = Left(x, Len(x) - 2)
End If
End If
End Function

This allows me to have more than one vlookup entry in one cell.

This works fine but what I also would like to do is to be able to colour the different line entries in a cell according to the type of entry it is.

The data comes from a list that I have created in another tab with columns for the Date, Event(s), Type and Comments. The text that appears in the cell with the multivlookup formula comes from the Event(s) column and is driven from the Date column. What I would like to do is to conditional format the different entries in this tab and have the multivlookup function copy both the text of the event and the colour of the text. Is this possible?

Thanks in advance for your help!

Tom