VLOOKUP Question
 

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.

VLOOKUP Question
 

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

VLOOKUP Question
 

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


"Rich K." wrote:

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

VLOOKUP Question
 

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...

VLOOKUP Question
 

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?

VLOOKUP Question
 

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

"Rich K." wrote:

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?

VLOOKUP Question
 

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

VLOOKUP Question
 

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

VLOOKUP Question
 

that is correct
replace with your sheet names

"Rich K." wrote:

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

VLOOKUP Question
 

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.

"Rich K." wrote:

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