Assuming you wanted to loop through Sheet2!A17:A20, find those values in
Sheet1!D37:D47 and return the formulas and formatting from column O of
Sheet1, you can use the Find method inside of a For/Each loop. Change
worksheet/range references as needed and be sure to practice with it on a
copy of your workbook.

Sub Test()
Const lngOffset As Long = 11
Dim rngSource As Range
Dim rngFound As Range
Dim rngCriteria As Range
Dim rngCell As Range

Set rngCriteria = Worksheets("Sheet2").Range("A17:A20")
Set rngSource = Worksheets("Sheet1").Range("D37:D47")

For Each rngCell In rngCriteria
Set rngFound = rngSource.Cells.Find( _
what:=rngCell.Value, _
after:=rngSource.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)

If Not rngFound Is Nothing Then
rngFound.Offset(0, lngOffset).Copy
rngCell.Offset(0, 1).PasteSpecial Paste:=xlFormulas
rngCell.Offset(0, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Set rngFound = Nothing
End If
Next rngCell

End Sub




"olrustyxlsuser" wrote:

update:
The formula now looks like this (cheers Pete_UK!)
=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13)-1,FALSE)),"",VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13 )-1,FALSE))

I also need the VBA to copy the formulas, not just the formats, from the
data table.




"olrustyxlsuser" wrote:

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!