Color Change

[CHML]

Hi. I have records A1: L252. I want all cells in these rows to change color when column H changes. What is a simple way to do that?

 

[Drrellik]

Conditional formatting

 

[CHML]

Hi, Thanks. I know about Conditional formatting. But how do I proceed to change color in all cells when column H changes?

 

[Drrellik]

Sorry for being short with my answer, but your question is rather vague.

If any cell in column H changes its value from Row 1 to Row 252, would you like for the color of all cells in range A1:L252 to change?

what color do they start with, and what color would you like them to change to?

also if a second cell from the H column changes after they have already changed color once, do you want them to change color again?

 

[CHML]

See the attached sample I just made manually and wish this Excel should do for me for the entire sheet. I don't care which color as long you see a difference in each 'Teachers' name'

 

[Craig_Moore]

Select row that you want to change color, select conditional formatting from the ribbon and click new rule, select "use formula to determine which cells to format" then use the following formula
=$H2="Teacher 1" then click the format button, click on fill, select a color from the options and click ok, then click ok again and then apply

do this reportedly until you have the number of teachers you require then press ok on the rules manager page

now click on format painter from the ribbon and select all other rows

hope this helps

Craig

 

[CHML]

Thanks, Craig, but Excel could probably act quicker. I would instead do something around the following formula. =$H2<>H1 Still, the issue is that Conditional Formatting only offers one color at a time, which makes all corresponding cells the same color.

 

[Drrellik]

Like Craig said you can have multiple conditions and make each teacher a different color using his answer

 

[Peter_SSs]

I don't care which color as long you see a difference in each 'Teachers' name'

I'm assuming this is just to make it easier to see where a teacher group starts and finishes. If that is the case, would you be happy with a solution like this that just alternates two colours?
I'm also assuming no gaps in the data in col H.

Use a vacant column to the right (I used M) and enter this formula from row 2 down as far as you might ever need. You can then hide this column.
Apply the two very simple Conditional Formatting rules shown.
In fact, if you are happy with the banding being one colour and white you can omit the orange CF setting.

If you do want a unique colour for each teacher then you will either need a lot of CF rules or else apply the colour banding with vba rather than Conditional Formatting.

24 03 12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								Teacher
2								Teacher 1					1
3								Teacher 1					1
4								Teacher 1					1
5								Teacher 1					1
6								Teacher 1					1
7								Teacher 1					1
8								Teacher 1					1
9								Teacher 2					0
10								Teacher 3					1
11								Teacher 3					1
12								Teacher 3					1
13								Teacher 3					1
14								Teacher 3					1
15								Teacher 4					0
16								Teacher 4					0
17								Teacher 4					0
18								Teacher 5					1
19								Teacher 5					1
20
21
22
23
24
25
CHML

Cell Formulas
Range	Formula
M2:M25	M2	=IF(H2="","",IF(H2=H1,M1,1-M1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:L25	Expression	=$M2=0	text	NO
A2:L25	Expression	=$M2	text	NO