conditional formating based on string

mbtaichi

Board Regular
Joined
Jan 5, 2016
Messages
71
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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:
Upvote 0
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.
 
Upvote 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.
Then will you have column headers for each day of the week in B1:G1?
 
Upvote 0
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)))

Capture2.jpg
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top