Look up Ref on two sheets and highlight a row range.

[TXJM]

I am looking for a way to find if a AlphaNumeric (like "21 02D") reference in column E on sheet 1 and compare if that Alphanumeric is also on a table on sheet 2.
If there is a match then highlight that row on sheet 1 from A:N.
And then continue to the next number in column E and repeat.

Any help is greatly appreciated.

Thank you,
TXJM

[etaf]

column E on sheet 1
a table on sheet 2

I will assume the table is in column A

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
A:AN on sheet 1

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=COUNTIF(Sheet2!$A:$A,$E1)>0

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

[TXJM]

Thank you for your reply. What I for got to mention was that a fresh copy of sheet 1 is supplied daily from a different department, as such I do not think a normal conditional formatting does work. I do copy that whole workbook sheet onto sheet 1 in this workbook and "scrub" to my needs. The table on sheet 2 is about 250 reference lines.

I do have come up with a part solution just not in VBA and it does not do the whole column E just one cell. **** =IF(ISNUMBER(MATCH($E8,$B$2:$B$250,1)),"True",) **** And then just replace "True" with a IF + CONDITIONAL FORMATTING like in the other example listed below :

Sub ChangeColor() ' Color flags TWD from 3 days to go
lRow = Range("F" & Rows.Count).End(xlUp).Row
Set MR = Range("F8:F" & lRow)
For Each cell In MR
If cell.Value <= 0 Then cell.Interior.ColorIndex = 3
If cell.Value = "1" Then cell.Interior.ColorIndex = 6
If cell.Value = "2" Then cell.Interior.ColorIndex = 6
If cell.Value = "3" Then cell.Interior.ColorIndex = 43

Next
End Sub

[etaf]

Sorry, I'm not a VBA expert, I'm sure someone else will reply soon to assist

[JOHN H. DAVIS]

Can you attach a workbook with some sample data to illustrate? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[TXJM]

See attached a small sample data.xlsx
Thanks

[TXJM]

How do I convert =IF(ISNUMBER(MATCH($E8,$B$2:$B$250,1)),"True",) into VBA and move down to the next row.

thank you,

[JOHN H. DAVIS]

How do I convert =IF(ISNUMBER(MATCH($E8,$B$2:$B$250,1)),"True",) into VBA and move down to the next row.

thank you,

For instance if you wanted to place it on Column H in Sheet2.

Please Login or Register  to view this content.

If you wanted to convert it to values after calculations then you'd just remove the comment.