Cond. Formatting w/ Formula not always working - Why?

[KelMac]

Let me try this again...

I have two spreadsheets (in 2 workbooks). One SS is a listing of Store Samples, the other is a Price List of products. On the pricing spreadsheet, I would like the cells in the Item number column to be highlighted if the cell contains text (regardless of case) that is found in the ItemNo column of the Store Samples.

I recently was able to get the following code to work… but it doesn’t work for some of the cells. Some are not highlighted even though they have text that is found in the samples column.
Code I am currently using: =COUNT(SEARCH(SAMPLES!$A$2:$A$230,PRICE!A1,3))

Is this the best formula I should be using, or does it just need to be tweaked?

Thanks in advance!!

Workbook: Store Samples – Tab: Material
|MFCTR       |PRODUCT      |COLLECTION    |TYPE              |ITEMNO     |COLOR/STYLE|
|Tarstrong   |Vinyl        |Simple        |Red House         |A9876      |RED        |
|Tarstrong   |Vinyl        |Simple        |Blue House        |A9875      |BLUE       |
|Tarstrong   |Vinyl        |Simple        |Green House       |C4567      |GREEN      |
|Tarstrong   |Vinyl        |Simple        |Yellow House      |C4568      |YELLOW     |
|Tarstrong   |Vinyl        |Simple        |Purple House      |B2020      |PURPLE     |
 
Workbook: Tarstrong Pricing – Tab: August
|MFCTR         |PRICE CLS     |CLS DESCRP   |ITEM #        |DESCRP 1    |DESCRP 2|
|Tarstrong     |LUX           |BEST         |TARB2020987   |HOUSE       |PURPLE  |
|Tarstrong     |LUX           |BETTER       |TARC4569123   |HOUSE       |BLACK   |
|Tarstrong     |LUX           |BEST         |TARA9876456   |HOUSE       |RED     |
|Tarstrong     |LUX           |GOOD         |TARA9879654   |HOUSE       |WHITE   |
|Tarstrong     |LUX           |BETTER       |TARA9875056   |HOUSE       |BLUE    |

 

[HolyExcel]

if i get it right, in Pricing Sheet in the column ITEM# the code is always starting at 4th position and is 4 characters long, correct?

If so try this in Column Item# on sheet Pricing as conditional formatting (select from cell D2 to last cell):

=if(vlookup(mid(D2,4,4),SAMPLES!$D$5:$D$230,1,0)<>"#N/A",TRUE,FALSE)

 

[KelMac]

if i get it right, in Pricing Sheet in the column ITEM# the code is always starting at 4th position and is 4 characters long, correct?

If so try this in Column Item# on sheet Pricing as conditional formatting (select from cell D2 to last cell):

=if(vlookup(mid(D2,4,4),SAMPLES!$D$5:$D$230,1,0)<>"#N/A",TRUE,FALSE)

HolyExcel... I could kiss you right now!!
The code is 5 characters long so I changed the 2nd 4 to a 5... but after that... it worked great!!! Thank you so much!!!

 

[HolyExcel]

glad it helped