If statement for data in columns

ratty9 - Mar 26, 2010 at 05:53 AM
 rattyp9 - Mar 29, 2010 at 06:11 AM

im a little stuck
I have 2 columns of data, i want to get the value of column 2 by searching data in the entire column 1. If column 1 matched what im looking for the the value in column 2 is returned...

below is the code i have used but it will only bring back values from the same line.
can anyone help me...?

code = column 1
G12 = seearch criteria
commodity = return value (column 2)


1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 26, 2010 at 11:33 PM
what is meant by "value in column 2 is returned... "

If the value in colum B is found in column A then that value in column B is colored yellow by this macro given below

Sub test() 
Dim r As Range, c As Range, cfind As Range 
ActiveSheet.Cells.Interior.ColorIndex = xlNone 
Set r = Range(Range("B2"), Range("B2").End(xlDown)) 
For Each c In r 
Set cfind = Columns("A:A").Cells.Find(what:=c.Value, lookat:=xlWhole) 
If Not cfind Is Nothing Then c.Interior.ColorIndex = 6 
Next c 
End Sub
Column 1 Column 2

APP 8081080
APR 8091000
AVO 8044000
CHERRY 8092095

not quite but the code will help me with another issue,, thanks you.

what im after is the value from column 2 if the my selected cell has the same value as column 1.

so for instance if D1 = APR
I want to search column 1 (any where as the row numbers will be different) and
return the value 8091000 next to it from column 2 .

it only works if the D1 is in the same row number as the columns.

i hope this is celarer...