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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
You may try this = OR(ISNUMBER(MATCH(D2&"*",A:A,0)),ISNUMBER(MATCH(D2&"*",B:B,0)))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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
 

ScottR1

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,580
Messages
5,548,866
Members
410,881
Latest member
toonces
Top