Dim cell as Range, res as Variant

for each cell in worksheets("sheet3").Range("A5:A105")
res = Application.Vlookup(cell.Value, _
Worksheets("sheet4").Range("M2:O100"),3,False)
if not iserror(res) then
Select Case lcase(res)
Case "number"
cell.offset(0,1).NumberFormat = "General"
Case "percent"
cell.offset(0,1).NumberFormat = "0.00%"
Case "time"
cell.offset(0,1).Numberformat = "hh:mm:ss"
End Select
End If
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
Hey guys

On sheet3 I have values in Range A5:A105. This Range
contains names of stats. I will have numbers(the raw
stat) in the next column (B5:B105).

On Sheet4 in range M2:M100 I have stat names. All the
stat names on sheet3 in Range A5:A105 will be in range
M2:M100 but all the stat names in Range M2:M100 may not be
in the list of stats on sheet3 (Range A5:A105). Because
of this, I need for a code to go through the range on
sheet3 (A5:A105) and look for the matching stat name in
the range on sheet4 (B5:B105). When it finds a match I
need the code on sheet4 to offset 2 columns to the right
and look at the value in the corresponding cell (value in
columnO). The value in ColumnO will either
say "number", "percent", or "time". This is how I need
the cell offset1 to the right on sheet3 to be formatted.

For example. The code is run. It first goes through
values on sheet3 range A5:A105. The value of cell A5 is
Productivity. The code then goes to sheet4 and finds the
stat name called Productivity. When it finds it, it then
offsets 2 columns over and sees the value is "number"

The code must then go back to sheet3 where stat
Productivity is located and then offset 1 column over to
cell B5 and format cell B5 as General Number. The code
must then loop through all the stats in Raange Sheet3
A5:A105 until there are no more stats in the range.

Number is to be formatted as General Number
Percent is to be formatted as Custom 0.00"%"
Time is to be formatted as time.

Thanx