Highlighting values from list in range?

[afc171]

Hi all,

It it possible to highlight the rows from A to J which match the numbers under column U in different colors?

So if 4200323937 appears in column I, highlight green. If 4200323083 appears highlight orange etc

ProductPath2.xlsm
	A	B	C		E	F	G	H	I	J	K									T	U
1	PRODUCT	QTY	BATCH		DOCUMENT	BIN	Whs Order	HU	Purchase Orders	BinP											Purchase Orders
2	prod1	2	INN1801		11081589	KDBCAT04	3000103980	111111110160356000	4200324186	18											4200323937
3	prod2	16	161544		11081538	KDBCAT05	3000103974	111111110160352000	4200323937	19											4200323083
4	prod3	16	160694		11081589	KDBCAT06	3000103980	111111110160356000	4200324186	20											4200321357
5	prod4	10	64589183		11081587	KDB10001	3000103978	111111110160354000	4200319010	21											4200324404
6	prod5	12	64588256		11076762	KDB10002	3000104037	111111110160286000	4200322121	22											4200324186
7	prod6	4	100006983		11087005	KDB10002	3000103990	111111110160412000	4200323937	22											4200322121
8	prod7	3	64590450		11076762	KDB10004	3000104037	111111110160286000	4200322121	24											4200319010
9	prod8	3	64590452		11076762	KDB10004	3000104037	111111110160286000	4200322121	24											4200321793
PRODUCTS

Cell Formulas
Range	Formula
J2:J9	J2	=VLOOKUP([@BIN],BINPATH!A:B,2,0)

Thank you

 

[Micron]

I would say yes, if you are willing to use code because I'm assuming there will be too many possibilities so conditional formatting can't be used. Someone else may know if there is another way. I'm also thinking that you could make U column values the same colour if found in column I so that both cells are orange (or whatever). However if the list of PO numbers is going to grow, you'd have to take another route or else you'll be forever editing the code - or will this be a one-off thing? If the former, I envision you colouring the cell then the code finds the value and applies the same colour to the cell where found.
How many rows in this list?

EDIT - just colouring cells means you have to go looking for them. Is there a reason why you can't just use Find on the ribbon, which will take you to the cell(s) that match?

 

[afc171]

The PO numbers could be a range of 1 - 20 numbers.
The reason for highlighting is that the products are picked by Column J then packed by column I.

 

[Micron]

I imagine this could be improved upon (e.g. find last row rather than choose entire I column, disable events, etc.). I went with the idea of copying the double clicked cell colour. Try setting U2 to yellow then double click U2.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim firstAddress As String

With ActiveSheet.Range("I:I")
    Set c = .Find(Target.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Color = Target.Interior.Color
            Set c = .FindNext(c)
            If c.Address = firstAddress Then
                Cancel = True
                Exit Sub
            End If
        Loop While Not c Is Nothing
    End If
End With

End Sub

 

[afc171]

Cheers mate that seems to work.
Is there a way to put it in a macro to run, only issue is assigning a different colour to each PO number

 

[Micron]

Not sure what you have in mind that will make it run? If that is to create what Excel users call a macro and do the same thing then that exact code won't run because there would not (might not be) Target or Cancel parameters. Can you explain what you want to do to make it run and what you mean by assigning colours to PO numbers? You'd have to do that in code at the very least. I chose copying the cell colour of the PO because it eliminates editing code to deal with colours associated with PO's. That also allows you to colour a PO cell, reuse colours, change colours, etc. and it will still find the PO and colour the cell.

 

[afc171]

What I did is create a conditional format for column i and if it matches numbers in column U (U2:U20) then colour in chosen colour. Only problem is since I selected the whole column i it now colours in empty cells aswell because if U17 has no PO number and is empty it will fill in all empty cells in column i with that colour for that cell.
Is there a way to only apply conditional formats within the table range?

 

[Micron]

Include the And function in your cf formula? The logic might be like If(And(U2=something,U2<>""),result if true, result if false).
Basically saying U2 cannot be an empty string as well as it has to be equal to something else (or whatever your first part is if it's not equals).
If that's not what you meant then I'm afraid I know next to nothing about the features of Excel tables.