#1
|
|||
|
|||
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 ) |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting | otuatail | Excel | 1 | 06-06-2012 05:07 AM |
Conditional Formatting. | Laurie B. | Excel | 6 | 04-09-2012 05:01 PM |
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 |
help with conditional formatting | Snvlsfoal | Excel | 3 | 07-03-2011 11:55 PM |