VLOOKUP Question

[Guest]

Access 2003 - not sure how do write the correct VBA code to accomplish this.
I have a small macro with hard-coded vendor numbers in it that if they are
found, turns the excel line either red or green. But the problem is that
these vendor numbers may change from time to time. Have a worksheet where
these test vendors are stored and want to access this worksheet to used these
instead of relying on the hard-coding.

He is my hard-coding macro:
Sub ColorRich()

x = ActiveCell.Row

Do While Cells(x, 3).Value <> ""
If (Cells(x, 3).Value = 2142) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 4
ElseIf (Cells(x, 3).Value = 2852) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 4
ElseIf (Cells(x, 3).Value = 4761) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 4
ElseIf (Cells(x, 3).Value = 6587) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 4
ElseIf (Cells(x, 3).Value = 2399) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 3
ElseIf (Cells(x, 3).Value = 9441) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 3
ElseIf (Cells(x, 3).Value = 10319) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 3
ElseIf (Cells(x, 3).Value = 12923) Then
Cells(x, 3).EntireRow.Interior.ColorIndex = 3
End If

x = x + 1

Loop

End Sub

And here is a snapshot of my test vendor file:

Column A
Vendor #:
2142
2399
2852
4761
6587
9441
10319
12923

The file I would run this macro against may have anywhere from 1 - x number
of lines so I would like the new macro to run until a blank line is found.

I am very new to VBA and tried to look thru the threads but couldn't put
enought together to figure this out. Thanks in advance for all your help and
suggestions.

 

[Guest]

Forgot to mention - there is a second column in the Status report that will
determine if red or green - is '*' then green else red.

Column A Column B
Vendor #: Email Vendor
2142 *
2399
2852 *
4761 *
6587 *
9441
10319
12923

 

[Guest]

conditional formatting will do this without a macro
select all
format as red
<format><conditional format>
change "cell value is" to "equation is"
=vlookup(C1,venderfile!A:B,2,0)="*"
set format as green.

using appropriate name for vendefile

 

[Guest]

Not sure I fully understand and possible I did not explain clearly. Not all
entries will be red or green. By default, all are not filled. Only ones in
the status report will be green (with '*') or red (without '*'). Will try to
work with what you gave me.

Normally don't use conditional formatting because I do work in blank
workbook and copy/paste final results into shared final workbook. But...

 

[Guest]

Tried working with Conditional Formating but when I clicked ok, got error
message "You may not use references to other worksheets or workbooks for
conditional formatting criteria'.

Format of expression was:

=vlookup(C2,'[EDI Status Report.xls]Test Vendors'!A:B,2,0)="*"

where EDI Status Report.xls is the workbook and Test Vendors is the
worksheet within that workbook.

Any other ideas? Or am I missing something?

 

[Guest]

forgot about that, sorry
to not use conditional, try

Sub colorrisch2()
Sheets("vender").Activate
n = Application.WorksheetFunction.Count(Columns(1))
Dim ven(n, 2)
For i = 1 To n
ven(i, 1) = Cells(i, 1)
ven(i, 2) = Cells(i, 2)
Next i
Sheets("data").Activate
x = 1
10 If Cells(x, 3) = "" Then GoTo 99
For i = 1 To n
If Cells(x, 3) = ven(i, 1) Then Cells(x,
3).EntireRow.Interior.ColorIndex = 3
If Cells(x, 3) = ven(i, 1) And ven(i, 2) = "*" Then Cells(x,
3).EntireRow.Interior.ColorIndex = 4
Next i
x = x + 1
GoTo 10
99
End Sub

 

[Guest]

BJ - getting compile error: on Dim ven(n, 2). The n is highlighted with the
message: constant expression required

 

[Guest]

Also, is "vender" on line two the name of the workbook and "data" is that
worksheet name? Again, I am somewhat of a newbie.

 

[Guest]

that is correct
replace with your sheet names

 

[Guest]

It may be a version issue, I use variables in dim statements
alternately what you could do is just count the number in your vender sheet
and enter that number in the macro dim statement
in this case don't bother with the count statement.