Solved excel 2010 conditional formatting, multiple columns

Microsoft Excel 2010
September 27, 2012 at 07:36:19
Specs: Windows XP
I'm trying to get multiple columns to change colour, dependent upon a row of cells.
So, spreadsheet is set up by week Column B is Wk 1 Cell Ref B2 says "1", Col c is Wk2 etc, Cell Ref C2 says "2", etc. Then I have on the next row down, the Month that week falls in to, so for B and C, they both show Month 1, as "1"
I would like the columns to highlighted different colours, dependent upon the Month, so it's easy to see at a glance which months are 4 or 5 week months.

If I highlight all 52 weeks as my Current selection, as soon as that IF statement returns B3:ba3=1. it colours the whole thing colour of the 1 statement. I would like it to colour the other months, different colours - is this possible, or do I have to format every column separately??


See More: excel 2010 conditional formatting, multiple columns

Report •


✔ Best Answer
September 27, 2012 at 09:55:08
OK, try this:

With your data as shown you will need to create 12 rules for
your Conditional Formatting.

This is for Conditional Formatting 2007:

Rule for First Month:

Select your cell or range of cells, even the whole sheet.
On the ribbon click Conditional Formatting
Click on New Rules, it’s near the bottom of the dialog box.
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=1 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for January
Click OK
Click OK

Rule for Second Month:

Select your cell or range of cells, even the whole sheet.
On the ribbon click Conditional Formatting
Click on Manage Rules
Click on New Rules, it’s near the bottom of the dialog box.
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=2 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for February
Click OK

Rule for Third Month:

Click on New Rules
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=3 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for March
Click OK

Do this for ALL 12 Month of the year, simply
change the end number in the formula for each month, as shown in Row 2.

See how that works out.

MIKE

http://www.skeptic.com/



#1
September 27, 2012 at 08:12:08
Sorry, you lost me.
Read this How-To and post a small sample of your spreadsheet.

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#2
September 27, 2012 at 08:13:31
Maybe I'm missing the concept, but in order to color a column (or group of columns) based on the values in Row 3, wouldn't you have to write 12 rules and choose a color for each rule, based on that value? Otherwise how would Excel know what color to use for which column?

Since I assume that these are static values for a given year, it seems like it would be easier to just manually color the 12 sets of columns that you are dealing with. You be done in less time than it took to post your question.

Am I missing something that makes it less simple than I am imagining?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
September 27, 2012 at 08:45:43
Week	6/1/12	13/1/12	20/1/12	27/1/12	3/2/12	10/2/12	17/2/12	24/2/12	2/3/12	9/3/12	16/3/12	23/3/12	30/3/12
Month	1	1	1	1	2	2	2	2	3	3	3	3	3

Some of the sheet (it has 52 weeks on, but the above will do for illustration purposes) shown above.
The workbook will be used for future years, so as the Months vary each year between 4 and 5 week months I wanted it to colour the months dependent upon the number in Row 3 - the Month No.
Yes I could just manually colour the columns in, but this is a multi page sheet with 52 columns for each one which will change every year.


Report •

Related Solutions

#4
September 27, 2012 at 09:55:08
✔ Best Answer
OK, try this:

With your data as shown you will need to create 12 rules for
your Conditional Formatting.

This is for Conditional Formatting 2007:

Rule for First Month:

Select your cell or range of cells, even the whole sheet.
On the ribbon click Conditional Formatting
Click on New Rules, it’s near the bottom of the dialog box.
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=1 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for January
Click OK
Click OK

Rule for Second Month:

Select your cell or range of cells, even the whole sheet.
On the ribbon click Conditional Formatting
Click on Manage Rules
Click on New Rules, it’s near the bottom of the dialog box.
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=2 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for February
Click OK

Rule for Third Month:

Click on New Rules
Click Use Formula to determine which cells to format.
Enter the formula:

=INDIRECT(ADDRESS(2,COLUMN()))=3 <<--Note the number

Click on the Format button
Select the Fill Tab
Select your pretty color for March
Click OK

Do this for ALL 12 Month of the year, simply
change the end number in the formula for each month, as shown in Row 2.

See how that works out.

MIKE

http://www.skeptic.com/


Report •

#5
September 27, 2012 at 10:11:06
FYI...

I think you need to use ADDRESS(3,COLUMN()) since the month numbers are in Row 3.

FYI (The Sequel)

This could be done with VBA by creating a column of 12 different colors and then referencing those cells and copying the Interior.ColorIndex to the columns with the weeks.

For example, if you put 12 different colors in A1:A12 then ran this code, it would copy the colors to the columns based on the month number. All Month 1 columns would match A1, all Month 2 columns would match A2, etc.

The assumption based on your posts is that your week numbers are in B2:B53, so the code is going to read the numbers in C2:C53 to determine which cell to copy the color from.

Sub ColorMyMonths()
 For wk = 2 To 53
  Columns(wk).Interior.ColorIndex = Cells(Cells(3, wk), 1).Interior.ColorIndex
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 27, 2012 at 10:51:13
I thought Row 2 was Months?
That's how it appears in the sample that was posted.
(There a no Row Numbers or Column Letters)

If the Months are in Row 3, then the corrected formula, as DerbyDad03 pointed out, is:

=INDIRECT(ADDRESS(3,COLUMN()))=1

MIKE

http://www.skeptic.com/


Report •

#7
September 27, 2012 at 11:01:04
The OP reads:

Column B is Wk 1 Cell Ref B2 says "1", 
Col c is Wk2 etc, Cell Ref C2 says "2", etc. 
Then I have on the next row down, the Month that week falls in to.

I'm assuming that the "next row down" from B2 is Row 3. It is on my spreadsheets ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
September 27, 2012 at 11:15:20
Going by the sample that was posted:

     A    B        C       D       E      F        G       H       I      J       
1) Week	6/1/12	13/1/12	20/1/12	27/1/12	3/2/12	10/2/12	17/2/12	24/2/12	2/3/12
2) Month     1       1       1       1      2        2        2      2       3 

Also, you really don't need 12 rules,
11 will suffice, as you can use the Default color white as your 12th Rule.

MIKE

http://www.skeptic.com/


Report •

#9
September 27, 2012 at 11:49:39
But the text right below the example says

"the months dependent upon the number in Row 3 - the Month No."

Between that and the text in the OP, I'm pretty sure that ADDRESS(3,COLUMN()) will be required.

BTW...I like the rule you offered. I always find the use of the ROW() and COLUMN() functions to be somewhat elegant. It makes me think that the developers were thinking ahead and realized that users would need a way to generate sequential numbers within a formula.

In most cases, the use of ROW() and COLUMN() really have nothing to do with the formula they are used in, they are simply there to generate a number that can then be manipulated with simple mathematical operators,

ROW() + 2
COLUMN() - 3
etc.

Nice!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
September 28, 2012 at 01:59:33
Brilliant!! Works perfectly, thank you so much.

Report •

Ask Question