Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2012, 09:19 AM
mrgy05 mrgy05 is offline Conditional Formatting per Week Windows XP Conditional Formatting per Week Office 2010 32bit
Novice
Conditional Formatting per Week
 
Join Date: Jun 2012
Posts: 1
mrgy05 is on a distinguished road
Default Conditional Formatting per Week

I'm working with a table separated per month on each sheet. There is a column for date and I would like to conditionally format every other week to easily identify them.



The catch: our working week is not Sunday - Saturday, it is Thursday - Wednesday.

How would this be possible?

I've search and found a formula for every other row, but would like every 7 rows based on Thursday - Wednesday. ( =MOD(ROW(),2)=1 )
Reply With Quote
  #2  
Old 06-06-2012, 03:53 PM
awstyle awstyle is offline Conditional Formatting per Week Mac OS X Conditional Formatting per Week Office for Mac 2011
Novice
 
Join Date: May 2012
Location: Cambridgeshire, UK
Posts: 6
awstyle is on a distinguished road
Default Conditional Formatting by week

Nearly there

I've attached an example. The main driver for this is the Week column (note formula from C9 down. Once that is set up, highlight the whole table (not the header row) and your conditional formatting formula will be =mod($C2,2)=1. Once set up you can hide the Week column.

Incidentally, thank you for asking. I have just taken delivery of Office 2011 for MAC. I use 2007 for Windows in work and have been using 2003 at home. Bill Gates has moved everything around again so I had to do a bit of detective work. It will, no doubut, come in useful in work in a day or so.

Kind regards.

Tony
Attached Files
File Type: xlsx ConFormat.xlsx (54.3 KB, 26 views)
Reply With Quote
  #3  
Old 06-07-2012, 10:38 AM
awstyle awstyle is offline Conditional Formatting per Week Mac OS X Conditional Formatting per Week Office for Mac 2011
Novice
 
Join Date: May 2012
Location: Cambridgeshire, UK
Posts: 6
awstyle is on a distinguished road
Default Conditional Formatting by Week

Thinking about my last post.

In my job I regularly have to work with spreadsheets of 80,000 plus rows and I thought people might find the following of use.

What if the groups you want to format have irregular numbers of rows? Have a look at the attached. The main formula for the formatting is the same as beforehand. It's all in Column B.

In this example we're highlighting each change in reference number. Start by entering 1 in cell B2 and then use the formula in B3 the rest of the way down. This also acts as a handy indicator of how many unique reference numbers there are.

Apply the formula as before. Job done!!

Good luck

Tony
Attached Files
File Type: xlsx Conformat2.xlsx (51.5 KB, 14 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting otuatail Excel 1 06-06-2012 05:07 AM
Conditional Formatting per Week Conditional Formatting. Laurie B. Excel 6 04-09-2012 05:01 PM
Conditional Formatting per Week Conditional formatting with AND, OR Lucky Excel 2 10-03-2011 11:41 PM
Conditional Formatting namedujour Excel 3 08-25-2011 01:46 PM
Conditional Formatting per Week help with conditional formatting Snvlsfoal Excel 3 07-03-2011 11:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft