• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting in 2010

K

Kassie

New Member
In XP, I set up a page-a-month calender in 2 columnsand 29-31 rows each.

I enter a starting date in Column E1. and fill col A from 1-however many days in the particular month, from A3. I then block eg A3:B33, and entered the following conditional formats: =WEEKDAY($E$1,1)+$A2=1 and also =WEEKDAY($E$1,1)+$A2=7. This worked excellently. Now I tried the same in 2010, and get an error message that I cannot use relative addresses in conditional formatting! How stupid can you get?
 
I can't replicate that error message in Excel 2010

Try clearing all Conditional Formats and reapply them
 
Told you I must be stupid! Tell me how you do it? I have tried several times, and every time I get the same error message. I have now deleted these rules, highlighted range A3:B33, and entered the same formulae again. This time around, it accepted the rule, but .... Only Cells A3:B3 (1/1/2011 and therefore a Saturday, were highlighted. I then selected A10:B10, selected to edit the formula, and it read =WEEKDAY($E$1,1)+$A2=1. In 2007 and all earlier editions it would have read $A9. So, please tell me how do I highlight all Saturdays and Sundays over 12 sheets, except to enter these two formulae 366 times each?
 
Awful sorry Hui, here is the correct formula, and when entered correctly, it still does not want to accept it. =WEEKDAY($E$1+$A2,1)=1
 
=WEEKDAY($E$1+$A2,1)=1. At first, I again got the error message. I again cleared all formatting rules, saved the file, closed and re-opened it, and then it worked! Thanks for your interest in any case Hui!
 
E1 is a date

What's is in A2 ?

I'm concerned about why your adding 2 dates together ?
 
As I said, I am trying to highlight Saturdays and Sundays. So, by using this formula, for the first of the month, I take the starting date, which is in $E$1. I add to this either a 0($A$2) or else the previous day's date, so that 7 January is made up of $E$1, which is a 1, plus the cell above 7, which is 6, to give me 7.
 
Try using a single Conditional Format not 2

=WEEKDAY($E$1+$A2,2)>5
 
Never thought of that one Hui, but thanks, it is working fine now! Your's would havve been a quicker option though!
 
You must log in or register to reply here.
Back
Top