advanced conditional formatting??

learning1984

New Member
Joined
Mar 2, 2016
Messages
14
Hi all. im rather stuck.
I have a spreadsheet which has coloumns as follows in sheet 1
Col A ColB ColC ColD
Dept intrusive Y/N Work to do name
Eng Y remove oven grids Steve
Eng Y Re-lay cooler Dave
hyg Y Clear out Lidder Paige
prod N Clear Mixer Dan

I then have another sheet (sheet 2) which shows all equipment that needs to be checked when work recommences.
in this sheet column A contains all equipment ie Oven, Cooler, Mixer, Lidder, Conveyor 1, Conveyor 2, Moulders.

What I want is, if the work done in sheet 1 is intrusive (Y) the work column is searched to see if it contains the equipment, ie remove oven grids contains the equipment oven. I want the row in sheet2 containing the word oven to be highlighted if there is intrusive work being carried out in the oven, and etc for other work that contains words in col A in sheet 2

sheet 2 contains 8 columns, info in the rest of this sheet is irrelevant, but want all 8 cells in the row to be highlighted.

is this possible?

thanks so much :)
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

learning1984

New Member
Joined
Mar 2, 2016
Messages
14
Im unsure if i'd be using a macro, or something along the lines of =SEARCH formula to look at the equipment word in column A sheet 2, and try and find that word in column C (work to do) in sheet 1, as well as then making sure a Y is in column =B in sheet one

please help
 

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
Hi learning1984,

I think this is what you're after:

=COUNTIFS(Sheet1!$B$2:$B$5,"Y",Sheet1!$C$2:$C$5,"*"&$A2&"*")>0

Add that as a conditional formatting rule in sheet 2 and apply it to the range you want formatted. You'll need to edit the ranges it's looking for in Sheet1 to match the size of your actual table.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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
Top