1

Using Excel 2010 and trying to work on some semi-complicated conditional formatting.
The spreadsheet is a roster system (don't ask why...the bosses insist it's the best).

I have 1 column which denotes the role for each person.
i.e. Person 1 - Level 1
Person 2 - Level 2
Person 3 - Level 1

And then I have a large area which denotes what hours they are working. Each cell is a half hour block, if the person is working, a 1 is placed in the block.

What I want to do is highlight work hours a certain color based on what role they are working. Something like green for Level 1, red for Level 2, etc., etc.

I was trying something like this:

=INDIRECT("D"&ROW())="Level 1"&VALUE=1

No luck though. I'm pretty sure the indirect portion works, because I already use it to set that person's entire row blue if their "role" is "un-available".

So yeah, if anyone could help me figure out how to do this, it would be much appreciated.

Improve this question
4
  • The peron names and roles are contained in a single cell: is this correct? If so, is it possible to seperate them into two columns? Commented Dec 30, 2011 at 3:50
  • Sorry, they are separate rows. One of the many improvements I've made :) Commented Dec 30, 2011 at 3:57
  • I know there are many rows, what I mean is when you say "I have 1 column which denotes the role for each person. i.e. Person 1 - Level 1" can this be split into 2 columns, one for the name and one for the role? Commented Dec 30, 2011 at 4:07
  • Yeah, sorry, I've conveyed that badly. There is a column for the person's name and a column for thier role Commented Dec 30, 2011 at 4:36

1 Answer 1

Reset to default
2

You simply need to add a Conditional Format rule for each Role, applying its unique colour.

Assuming you can split the name and role into seperate columns

Rule is a "Use a formula to determine which cells to format" type, in the general form of

=AND(Cell is 1, Role = RoleName)

Eg, if Roles are in column B, and cell to format is C2, for "Role 2"

=AND(C2<>"",$B2="Level 2")
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

A conditional formatting rule needs to be more generic that that though. I can't have a rule for cell F13 to AI41 in the format you've specified. I tried this; =AND(F13:AI41=1,D13:D41="Level 1") No luck on this...
Why are you trying to specify a range instead of a cell?
Because the conditional formatting needs to be applied to that entire range. I'm not sure how else I would do that?
Select a single cell in the range you want to format, apply the format using the formula in my answer (for cell F13 =AND(F13=1,$D13="Level 1") Note the $ used to anchor the Role column). Then either use the "Aplies To" field in the Cond Format dialog or use the Format painter to apply it to all cells in the required range

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.