colour coding alternate weeks

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

I am entering dates every week into a spreadsheet. How can I
automatically colour a date on week 1 as red and week 2 as green for
all alternate weeks?
 
Check your other post.
I am entering dates every week into a spreadsheet. How can I
automatically colour a date on week 1 as red and week 2 as green for
all alternate weeks?
Click to expand...
 
I am entering dates every week into a spreadsheet. How can I
automatically colour a date on week 1 as red and week 2 as green for
all alternate weeks?
Click to expand...

Hi Lynn,

Go to Conditional formatting dialog (Menu > Format > Conditional
Formatting)
Change the “cell value is” to “formula is”
Now, if you want to highlight alternate rows, the formula can go
something like this,
=MOD(ROW(),2)=0

Also, if you want to highlight alternate columns instead of rows you
can use the column() formula.
What if you want to change background color of every 3rd row instead,
just use
=MOD(ROW(),3)=0
instead.

In case of filtering use
=MOD(SUBTOTAL(3,$A1:$A$2),2)=0

For more information
- http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/23ad8a26f7fc4248?hl=en
- http://www.cpearson.com/Excel/banding.aspx
- http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/


Have fun, cheers
Michael
 
Hi,

I need the columns to be coloured based on date entered. so the
formula cannot be based on alternate rows
eg. a date on week 1 as red and week 2 as green , a date on week 3 as
red, a date on week4 as green, etc..etc...
 
Hi,

I need the columns to be coloured based on date entered. so the
formula cannot be based on alternate rows
eg. a date on week 1 as red and week 2 as green , a date on week 3 as
red, a date on week4 as green, etc..etc...
Click to expand...

Lynn,
search in the internet for a weekNo() function and replace the row/
column with that UDF.
Cheers Michael
 
Hi,

I need the columns to be coloured based on date entered. so the
formula cannot be based on alternate rows
eg. a date on week 1 as red and week 2 as green , a date on week 3 as
red, a date on week4 as green, etc..etc...
Click to expand...

Lynn,
search in the internet for a weekNo() function and replace the row/
column with that UDF.
Cheers Michael
 
i am not able to use weeknum in conditional formating

do you have a formula for determining odd and even weeks?
 
Why not use weekNo() in a separate workbook and make a table to be used in a
lookup function?

Then copy the table into your working spreadsheet and use lookup in
cond.format for coloring in with reference to the date.

There is a lot of info online about week numbers:

Google: calculate calendar week numbers
Formulas:
http://www.tondering.dk/claus/cal/node8.html#SECTION00870000000000000000


Lynn,
search in the internet for a weekNo() function and replace the row/
column with that UDF.
Cheers Michael
Click to expand...

Lynn,
I did the search for you ;-)
http://www.cpearson.com/excel/weeknum.htm

Cheers Michael
 
Don't think i can do this, back to my request, I am entering dates
every week into a spreadsheet. How can I
automatically colour a date on week 1 as red and week 2 as green, week
3 blue, week 4 yellow.
 
Back
Top