# conditional formating based on string

#### mbtaichi

##### Board Regular
Is it possible to use conditional formatting to highlight cells based on part of a string from cells found using VLOOKUP?
Sheet 1

 Name 1 Monday Tuesday Wednesday Thursday Friday Saturday Name 2 Monday Tuesday Wednesday Thursday Friday Saturday Name 3 Monday Tuesday Wednesday Thursday Friday Saturday

<tbody>
</tbody>

Sheet 2

 Name 1 THFSA Name 2 TUWTHF Name 3 MTUWTHFSA

<tbody>
</tbody>

M=Monday, TU=Tuesday, W=Wednesday, TH=Thursday, F=Friday, SA=Saturday

If “Name 1” is in A1 on sheet 1 VLOOKUP will look in the range A1:C3 on sheet 2. Then read the text in the in cells in column C then highlight only the cells for the days required on sheet 1.

Example of result returned to Sheet 1

 Name 1 Monday Tuesday Wednesday Thursday Friday Saturday Name 2 Monday Tuesday Wednesday Thursday Friday Saturday Name 3 Monday Tuesday Wednesday Thursday Friday Saturday

<tbody>
</tbody>

I think I am supposed to use ISNUMBER SEARCH nested in VLOOKUP but don’t know how.
Any help is greatly appreciated.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try using the following CF rule for range B1:G3 on Sheet 1:

=ISNUMBER(SEARCH(LEFT(B1,ISODD(COLUMN(B1))+1),VLOOKUP(\$A1,'Sheet 2'!\$A\$1:\$C\$3,3,0)))

Last edited:
Try using the following CF rule for range B1:G3 on Sheet 1:

=ISNUMBER(SEARCH(LEFT(B1,ISODD(COLUMN(B1))+1),VLOOKUP(\$A1,'Sheet 2'!\$A\$1:\$C\$3,3,0)))

Tetra 201 Thank you
Really sorry but I guess I have asked the question in the wrong way. The cells on sheet 1will not contain the days of the week but time values such as 07:30-15:30.
Again I apologize for not asking the question correctly in the first instance.

Tetra 201 Thank you
Really sorry but I guess I have asked the question in the wrong way. The cells on sheet 1will not contain the days of the week but time values such as 07:30-15:30.
Again I apologize for not asking the question correctly in the first instance.
Then will you have column headers for each day of the week in B1:G1?

Yes there are column headers in those cells

Yes there are column headers in those cells
Try using the following CF rule for range B2:G4 on Sheet 1:

=ISNUMBER(SEARCH(LEFT(B\$1,ISODD(COLUMN(B\$1))+1),VLOOKUP(\$A2,'Sheet 2'!\$A\$1:\$C\$3,3,0)))

Thank you

Replies
2
Views
201
Replies
4
Views
310
Replies
3
Views
399
Replies
3
Views
546
Replies
5
Views
427

1,196,329
Messages
6,014,678
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back