Sharad,

The last example looks to have some possibilities, I'll set up and test in
my files. Now you've given the idea, I recall code CASE where I could set
up the criteria.

Thanks, Rob

"Sharad Naik" wrote in message
...
Thoug i did not understand fully when exactly you want to format the cell,
I assume that if the VLOOKUP succeeds you want to format that 5 column,
else you want to leave it as it is.
I also assume that presently you are doing VLOOKP in the sheet itself with
standard VLOOKUP formula
entered and drag-copied?
If this is true then where the VLOOUP did not succeed it will show "N#A"

So you can write code like below

For each c In Workbooks("Text").Worksheets("xyz").Range("B:B").C ells
If Not c.Text = "N#A" Then 'note: it should be c.Text not c.Value
c.Offset(0,5).NumberFormat = "whatever"
End If
Next
Ofcource you can refer to the column until the last used row, I just
skipped that part.

You can put it in workbook open event or make it a module macro and run
whenever
you feel.

But in case you are doing it different way and "N#A" does not appear in
the cell
( many times I suppress "N#A" adding IF( ISERR(VLOOKUP .. )
Then you can make code in VBA to check if VLOOKUP is an error
and if not then format the number. e.g.:

Dim c
On Error Resume Next
For Each c In Workbooks("Text").Worksheets("xyz").Range("A:A1")
If Not IsError(Application.WorksheetFunction.VLookup _
(c.Value, Workbooks("Master").Worksheets("xyz") _
.Range("A:B"), 2, False)) Then
c.Offset(0, 6).NumberFormat = "whatever"
End If
Next c

Note: I didn't refer to the ranges properly in above example.

Sharad


"Rob" wrote in message
...
Excel 2000

I have two files, one master and one created by opening a text file. The
master file has a list of unique product codes and descriptions in two
columns, the text that is opened has the product codes and I add during
the opening process a look up to the product code to return the product
description, this all works fine. The text file after opening is 7
columns, product code, product description and 5 columns of numbers.

What I need to do is format the cells (numbers) in the last 5 columns of
the newly opened text file. My thoughts are to add a third column to the
master file whereby I'll have 3 columns: product code, product
description and number format, in the number format column I would format
the cell with the required number format. My issue is, how using VBA do
I format the cells in the newly opened text file where the product code
matches that of the corresponding product code in the master file. The
text file can have many of the same product codes and in some instances,
not all of the product codes.

I had also thought of Conditioning Format but alas, this doesn't cover
number formatting.

Any pointers, snippets of code most welcome.

Thanks, Rob