lookup value & formatting

  • Thread starter Thread starter Simon Topping
  • Start date Start date
S

Simon Topping

Hi,

Is it possible to undertake a vlookup function or such
like which not only imports the value that is found, but
also the format of the value, be it a different font or
colour instance?

Thanks

Simon
 
Only by using VBA. I suggest the following :-

1. Select cell with value
2. Find the value in the lookup sheet
3. Copy the cell containing the return value.
3. Go back to the original sheet & Paste (with format)

Something like this (you will need to amend as necessary) :-

'-------------------------------------------------------------------
Sub GET_LOOKUPVALUE()
Dim MyVariable
Dim FoundCell As Object
'-------------------------------
MyVariable = ActiveCell.Value
Set FoundCell = Worksheets("Sheet1").Columns(1).Find _
(what:=MyVariable, lookat:=xlWhole)
If FoundCell Is Nothing Then
MsgBox ("not found.")
Else
FoundCell.Offset(0, 1).Copy
ActiveSheet.Paste Destination:=ActiveCell.Offset(0, 1)
End If
End Sub
'--------------------------------------------------------------------
 
try something like this where you are doing a lookup for b

Sub copyitall()
With Range("b7")
..Value = Range("a1:a12").Find("b").Offset(, 1)
..Interior.ColorIndex = Range("a1:a12") _
.Find("b").Offset(, 1).Interior.ColorIndex
End With
End Sub
 
Back
Top