Conditional Formatting of Partial Data

[ScottR1]

I’m looking for help on how to conditionally format several worksheets in a workbook. Each worksheet already has a different type of conditional formatting in areas that I would not want affected by the new conditional formatting.

I have two columns (A and B) with approximately 50,000 cells. Each cell (except row 1) in these columns contains a term in the following format: ABCD-EF-012345-678910. The numbers are the only thing that change in each cell and there are several duplicates of each term in the columns. The location of dashes and the ABCD-EF are the same in each of the cells.

I also have a column D that contains approximately 100 rows of partial matches of unique terms from columns (A and B) that I will continually be adding terms to. The column D cells contains a term in the following format: ABCD-EF-012345.

I want to conditionally format (with the same color) all cells in columns A and B that match the unique terms of Column D. I have placed an example below.

It would be preferable if I could enter a formula into the conditional formatting field that says: Format values where this formula is true

Thanks!

 

[GraH]

Hi,
You may try this = OR(ISNUMBER(MATCH(D2&"*",A:A,0)),ISNUMBER(MATCH(D2&"*",B:B,0)))

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. (Hence my very small sample below. )

Select from A2:Bx and apply this CF rule and see how it goes.

20 04 24.xlsm
	A	B	C	D
1	Title A	Title B		Title D
2	ABCD-EF-012345-678910	ABCD-EF-555555-777777		ABCD-EF-012345
3		ABCD-EF-012345-678910		ABCD-EF-189703
4	ABCD-EF-012345-678910	ABCD-EF-897341-145987		ABCD-EF-897341
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B4	Expression	=AND(A2<>"",MATCH(LEFT(A2,14),$D:$D,0))	text	NO

 

[ScottR1]

Thank you both very much for your help! This is exactly what I was hoping for. I was able to successfully employ Peter_SSs' formula. I can't tell you how much I appreciate your help. You have saved me several days of work over the next couple of months and my wrist and clicking finger thank you too!

I will be sure to remember to look at XL2BB before posting again as I can see it is a pain to help without it being used.

Cheers!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.