Colouring Rows based on different file path

Question

I have mapped the folder structure of a shared network drive and I would like to colour rows based on specific folders.

The data I have is as follows:

y:\5 Dublin Square\
y:\5 Dublin Square\Costs\
y:\5 Dublin Square\Drawings and Photos\
y:\5 Dublin Square\E-mails\
y:\5 Dublin Square\General Correspondence\
y:\5 Dublin Square\Handover Certificate\
y:\5 Dublin Square\Health and Safety\
y:\5 Dublin Square\Legal and Contract\
y:\5 Dublin Square\Minutes\
y:\5 Dublin Square\Property and Report\
y:\ABW 01 - Meath, Station Rebuild\
y:\ABW 01 - Meath, Station Rebuild\Costs\
y:\ABW 01 - Meath, Station Rebuild\Drawings and Photos\
y:\ABW 01 - Meath, Station Rebuild\Health Safety and Environmental\
y:\ABW 01 - Meath, Station Rebuild\Land Acquisition and Transfer\
y:\ABW 01 - Meath, Station Rebuild\Legal\
y:\ABW 01 - Meath, Station Rebuild\Maintenance and Security\

I would like the colour the rows that have 'Dublin Square' in them as one colour and the rows with 'ABW 01' or 'Meath'as another colour.

In total I have 104 seperate folders ordered like the ones above.

Any help would be greatly appreicated.

Thank you.

Answer 1

Try the following conditional formatting formulas:

• =MID(A1,6,13)="Dublin Square"

  and

• =MID(A1,4,6)="ABW 01"

Edit: These are more flexible and perhaps more useful:

• =SEARCH("Dublin Square",A1)>=1

• =SEARCH("ABW 01",A1)>=1

Edit: Based on the comment about 102 sheets with similar layouts here is a more general answer, which assumes the original list of links to be in column A.

• Choose the first cell of an empty column, I chose H1 and populate with the formula =MID(A1,FIND("\",A1)+1,FIND("\",MID(A1,FIND("\",A1)+1,99))-1) then fill down the same number of rows as those filled by the links.

  this will extract the text between the "\"'s.

• Choose the second cell of another empty column, I chose L2, and populate with the formula =IFERROR(INDEX(H$1:H$17,MATCH(SUM(COUNTIF(L$1:L1,H$1:H$17)),COUNTIF(H$1:H$17,"<"&H$1:H$17),0)),"") and fill down to the next cell (this assumes two colors are going to be needed, per the sample given).

  Finally conditionally format the original list of links with the formulas:

• =SEARCH(L$2,A1)>=1 (fill = some color)

  and

• =SEARCH(L$3,A1)>=1 (fill = some other color).

  I tried to replace H$1:H$17 in the IFERROR(... formula with the MID(... formula, in order to reduce this to a two step process. Unfortunately I kept getting an Error in Formula message, perhaps someone here will know how to do that however.