Conditional format Multiple columns w multiple triggers

Question

I managed to get a rule set up to change the color of a column based on a cell in the adjacent column.  I want to replicate this across the sheet without doing 30 rules. When I copy the columns the rules do not follow.   There are 30 non contiguous columns each governed by a different cell.  Please tell me this doesn't require 30 rules!

I vaguely remember in the past being able to change cell colors without this rule structure.  I don't know if I just remember wrong or if perhaps conditional formatting worked more easily in previous releases (Im now using 2007). 

In the example below, the yellow color(B) is triggered by the cell in the last row of colmn D not being = 11 and likewise column F is not yellow because the last cell in colun H is = 11

	Tue				Wed				Thu				Fri
	08/31				09/01				09/02				09/03
a	b	c	d	e	f	g	h	i	j	k	l	m	n
	7	1			7	1			7	1			7	1
		0				0				0				0
		0			3	10				0			3	10
	7	1	2			0	11		3	10	11			0	11

Answer 1

I would suggest to upload a sample file with your requirements on skydrive, so that we can work on it

BLACK BARRON

---

P.S : Please post back with the status of the issue. If this post was helpful to you, please vote for my post If this post has helped you resolve your issue, please mark my post as answered.

Answer 2

In this section of the spreadsheet if the value in a the cell at the bottom of column D (value 2 in this case) is not 11, then column B is set to yellow. There is a CONDITIONAL FORMATTING RULE that makes the column yellow.

There are 11's in columns H and L so columns G and K respectively would NOT be yellow.

The problem is I dont want to have to set up RULES for every set of columns.  I want to be able to have something like a formula that I enter once then replicate across the sheet, not 30 separate rules.

Answer 3

First let me apologise for the example not showing the yellow color i reference in the text.  It was there when I did the cut and paste but seemed to disappear when the post was published.

Now regarding your response.  I have no idea what a format painter is, can you help me out with that?

Ed

Answer 4

How exactly did you set up the formula for column B?  In particular, did you use a formula?  If so, what?

 

 

I managed to get a rule set up to change the color of a column based on a cell in the adjacent column.  I want to replicate this across the sheet without doing 30 rules. When I copy the columns the rules do not follow.   There are 30 non contiguous columns each governed by a different cell.  Please tell me this doesn't require 30 rules!

I vaguely remember in the past being able to change cell colors without this rule structure.  I don't know if I just remember wrong or if perhaps conditional formatting worked more easily in previous releases (Im now using 2007). 

In the example below, the yellow color(B) is triggered by the cell in the last row of colmn D not being = 11 and likewise column F is not yellow because the last cell in colun H is = 11

 

	Tue				Wed				Thu				Fri
	08/31				09/01				09/02				09/03
a	b	c	d	e	f	g	h	i	j	k	l	m	n
	7	1			7	1			7	1			7	1
		0				0				0				0
		0			3	10				0			3	10
	7	1	2			0	11		3	10	11			0	11

 

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 5

Copy the formatting using the format painter to solve your problem

BLACK BARRON

---

P.S : Please post back with the status of the issue. If this post was helpful to you, please vote for my post If this post has helped you resolve your issue, please mark my post as answered.