PDA

View Full Version : Return Value with color



vishwakarma
05-02-2011, 03:49 AM
Hello Guys,

Everyone knows that we use Vlookup for retrieving the values from a table. Can we use the same function for retrieving the both value and cell color as well.



Thanks,

Bob Phillips
05-02-2011, 05:27 AM
Not VLOOKUP, you need VBA.

shrivallabha
05-02-2011, 09:31 AM
There's something wrong with the following function and I've struggled for last half an hour :banghead: .

Can't figure out:
Option Explicit
Public Function FormatVlookup(rValue As Range, rLookupArray As Range, iCol As Integer, iMatch As Integer)
Dim rfValue As Range
'**************************************************************************
'Finding the cell
'**************************************************************************
Set rfValue = rLookupArray.Find(What:=rValue.Value, LookIn:=xlFormulas, Lookat:=xlWhole)
If Not rfValue Is Nothing Then
FormatVlookup = rfValue.Offset(, iCol - 1).Value
rValue.Interior = rfValue.Offset(, iCol - 1).Interior
End If
End Function

Somehow I feel that cranking out a sub routine will be much easier than function route.

Bob Phillips
05-02-2011, 10:33 AM
The problem is that you cannot set worksheet properties from a UDF.

vishwakarma
05-03-2011, 09:45 PM
Hello Guys,

anyone got the solution?


Best,

Bob Phillips
05-04-2011, 12:42 AM
There is no foolproof way that I can see. You can't use a UDF. You could use worksheet event code that sets F2 when B2 changes but that code wouldn't work on a colour change.