Conditional Formatting of Partial Data

ScottR1

New Member
Joined
Apr 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!

1587665222841.png
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,
You may try this = OR(ISNUMBER(MATCH(D2&"*",A:A,0)),ISNUMBER(MATCH(D2&"*",B:B,0)))
 
Upvote 0
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
ABCD
1Title ATitle BTitle D
2ABCD-EF-012345-678910ABCD-EF-555555-777777ABCD-EF-012345
3ABCD-EF-012345-678910ABCD-EF-189703
4ABCD-EF-012345-678910ABCD-EF-897341-145987ABCD-EF-897341
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B4Expression=AND(A2<>"",MATCH(LEFT(A2,14),$D:$D,0))textNO
 
Upvote 0
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!
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top