How to find text in cell and change font color if found

Hi,

New to forum and Excel! And I have stumbled across a major problem...

I have a long list of all available products in one cell (D2) and a column of bought products (B2-B2900).
I need to find a way to look in D2 after the text that is in B2 (example "Loxa") and if the word Loxa is found I want Excel to change the font color of just that word in D2. See of attached file "workbook1".

Is there any way to accomplish that?

I have only tried the most basic functions like IF and FIND but I don't think that's going to cut it...

I'm grateful for any kind of help or tips! Thank you for reading

Regards,
Caroline

I am not sure I am getting it.

Give an example of something that is found, and what would be coloured as a result.

I'm attaching the file again with weak (and maybe useless) information of what I want.

I want to find the word "Loxa" in B2, in the long list in D2 that contains all products. When it finds the word, I want the word "Loxa" (in D2) to change font color from black to blue while the rest of the text remains black. I can't write =FIND("Loxa",D2) because I have to go through the entire B-column (2900 cells). The result should end up in C2 with the whole list from D2 where the only change is that one word (or product) has changed color.

Does that make sense or am I just beging confusing? :/

I'm sorry for the weak explanation.

See the attachment I added to post #4

I'm sorry, that is not quite what I was after.

I've made a "fake" workbook (see attached file) where you can see how the results should look. For this purpose I shortened the list in D2 to only the first 7 products.


/Caroline

Please Login or Register  to view this content.

Oh my God, that sounds amazing, abousetta!

Wow, Bob, that looks complicated, haha. I'm going to test that right away! Thank you very much.

I ran the code but it differ a little from what I need:

See my attachment for an example of the code result and my example (where, again, the list is shorter than the original). When searching in B2 it should only change that word (Loxa Startbatteri 90AH) and not all the words that's in the B2-list , and when it goes down to B3 it should only change that word (Loxa Startbatteri 110Ah) and so on.

I think it shows in the file what I mean, but just for clarification; I don't mean that the words in B2 should be red, that part of the code was correct.


Regards,
Caroline

You've totally lost me now, I am not clear as to whether you want to highlight the values in an existing list, or to create the list. If the former, why does your example list look different to the original list? If the latter, where do all of those values come from?

Don't you just love it when the OP gives you an example to start with that is nothing like the real data?

I'm sorry about the confusion.
I shortened the list so it would be easier to read. I remade it with the original data in this attachment. I want to generate a list from D2 where certain words are colored according to what
text is in B2-B2900. This list should stretch from C2-C2900 or any other column.

Maybe this new example can clarify things better...

I know I'm making it very difficult for myself, even more so now that I hear everybody else saying the it's hard to pull off =)
I have tried conditional formatting and it doesn't work for what I'm trying to do. The thing is, it doesn't matter that I can't read all the products in the long list, since am going to upload the whole sheet to XMPie and generate a variable print, where I will be able to see the whole list.
But, as you pointed out, this seems to be the hard way so I'm going to try and find a different solution.

Thank you for all your help!