Mark cells according to multiple search strings in different tabs

[nachtmahr]

Hello there,

I have a rather complicated question regarding Excel – I am not sure if it can be solved by using MACRO or a simple formula, or if it cannot be solved at all, but I want to try anyway

I have the following situation:

• I have several tabs with a lot of data in it. The data in column A of each tab consists of text strings which includes (among others) document number, document type (e.g. CV) and names of people (e.g. 20.100.20 CV 2017-01-22 Masters English)
• On a separate tab I make a list of names and document types, it looks something like this:

Name	CV	PN
Masters
Parker

• What I would like to do now is to mark the CV cells in this small table green if the following things are true:
• -> in any of the other tabs of the excel sheet I can find both text strings for document type and name (e.g. “CV” and “Masters”) within the same cell in any cell of column A
• -> if this is not true, then the cell should be marked red

Is there a solution for this?

Thank you and kind regards,
nachtmahr

[NickyC]

Hi
this macro is a bit clunky but should do the trick

it assumes your names are in column 1 and documents are in row 1 of a range named "MyTable"

it is case sensitive, and I have written it to search for " PN " not "PN" (with spaces before and after) so it doesn't return a false positive for eg "grapnel" (ie words containing "pn")

Please Login or Register  to view this content.