Help with Conditional Formatting!

elsopix

New Member
Joined
Jan 10, 2015
Messages
16
Hi, Hope you can help!

I am trying to simplify producing Rotas at work. What I have done is create a lay out with drop down lists refering to the staff. However some staff have different conditions, breaks etc. What I would like is to have the staff formatted in colour depending on what pool they are from.

So I have 1 workbook, with 8 sheets, one for each day of the week and one with names on for a "lookup"

I have the names grouped together on the look up. So I want to format every cell on the other sheets to say "if the name in this cell is from column X (on the lookup sheet) make it blue"

I am not finding a rule that will sort it from google. I can get it done on one cell but i cant them paint it across all the cells.

I will need 7 such rules for the different categories this will need to apply to all cells.

really hope you can help - Excel 2010 Pro

thanks
Elso
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

chrispy

Board Regular
Joined
Apr 26, 2008
Messages
73
In this example the employee names are on Sheet1 in columns A-G. On the other sheets (Mon-Sun) the firs name is in cell A2.
In the conditional formatting dialog box select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$A:$A,0) Then select the desired format
Open the conditional formatting dialog box and select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$B:$B,0) Then enter the desired format
Repeat for the five addition categories.
Highlight the formatted cell and click format painter
Remember, if you have a dollar sign before the 2 the format painter will not expand the conditional formatting correctly.

Hope this helps.
 

elsopix

New Member
Joined
Jan 10, 2015
Messages
16
In this example the employee names are on Sheet1 in columns A-G. On the other sheets (Mon-Sun) the firs name is in cell A2.
In the conditional formatting dialog box select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$A:$A,0) Then select the desired format
Open the conditional formatting dialog box and select "Use a formula to determine which cells to format".
Enter =OR(MATCH($A2,Sheet1!$B:$B,0) Then enter the desired format
Repeat for the five addition categories.
Highlight the formatted cell and click format painter
Remember, if you have a dollar sign before the 2 the format painter will not expand the conditional formatting correctly.

Hope this helps.

Thanks for getting back to me so quickly! Super impressed! Unfortunately this does not appear to be working - heavens knows why.
1) There is a bracket missing at the end - i added that.
2) When I paint the formula (using the cross cursor on the bottom right of the cell) it does not appear to be working on the lower rows
3) on the top row it is highlighting the whole row when I select one name.

Any ideas where I may be going wrong?

All the best
Elso
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
@ Chrispy, why are you using OR in those formulas (I see no need for that)?

@ elsopix which version of excel are you using (or, if I read correctly - are you using google docs)?

To apply CF to a range (at least in excel), you can use the 1st CF window to define the range
 

elsopix

New Member
Joined
Jan 10, 2015
Messages
16
Hi - Excel 2010 pro. Not google Docs! can you expand on how one would define the range?
Thanks
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
These are my general instructions for using CF....
If you are familiar with CF, ignore the 1st 2 points
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter your rule formula

edit: If you alre4ady have the rule set up, go back into the CF window, and re-set the range using the "applies to" box
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

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