Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Conditional Formating

Posted by Jessica on August 15, 2001 8:40 AM
I have a workdays listed on column "B" and in a another column (column M) I have total hours worked on that workday.
Column B Column M
Sunday 0 hrs
Monday 8 hrs

Right now I have a conditional format that keeps the total hours worked highlighted in yellow until it reaches 8 (8 hours worked per day). My problem is on Weekends I don't want those cells to be subject to the conditional format. How can I have it hightlight cells that are less than 8 hours and are not a Saturday or Sunday? I appreciate any assistance you can give me.

Thanks sooooo much!


Re: Conditional Formating

Posted by Mark W. on August 15, 2001 8:44 AM
Is Saturday/Sunday a text value (e.g., "Saturday")
or a date value formatted as "dddd"?


Re: Conditional Formating

Posted by Jessica on August 15, 2001 9:07 AM
It is a date value "dddd", it autofills from a given date.


Re: Conditional Formating

Posted by Mark W. on August 15, 2001 10:00 AM
Setup a "Formula Is" condition using the formula...

=AND($M2<8,NOT(OR(WEEKDAY($B2,2)=6,WEEKDAY($B2,2)=7)))


Re: Conditional Formating

Posted by Jessica on August 15, 2001 10:36 AM
It didn't seem to work. I replaced the "2s" with a "10s" since that is what row I am working in, would that create a problem? Here is what I changed: =AND($M2<8,NOT(OR(WEEKDAY($B10,10)=6,WEEKDAY($B10,10)=7)))

This is how it is set up:
Column B Column C Column M
Sunday Aug 19 0 hrs
Monday Aug 20 9 hrs
The weekday in column b calculates from corresponding date in column C.

I really appreciate your help. If this is taking too much of your time, don't worry about it.


Re: Conditional Formating

Posted by Mark W. on August 15, 2001 11:31 AM
You missed one... "$M2<8"


Re: Conditional Formating

Posted by Jessica on August 15, 2001 12:36 PM
I'm sorry, it still doesn't work. I guess I should just give up. I'm really sorry for taking so much of your time.


Don't give up... :0

Posted by Mark W. on August 15, 2001 1:15 PM
I didn't notice it before, but your revisions to
my formula -- also changed the 2nd argument to
the WEEKDAY() formula!! It should be 2 -- not 10.

So do this... select all your data rows leaving
the active cell in cell A2 (or 2nd row of the
leftmost visible column) then make these corrections
to your conditional formula.

My fingers are crossed... : ) I'm sorry, it still doesn't work. I guess I should just give up. I'm really sorry for taking so much of your time.


One more thing!!!

Posted by Mark W. on August 15, 2001 1:18 PM
I should have looked more carefully -- you also
messed up the cell reference in column B. Your
conditional formula should look EXACTLY like
this...

AND($M2<8,NOT(OR(WEEKDAY($B2,2)=6,WEEKDAY($B2,2)=7)))

...and follow my instructions below! I didn't notice it before, but your revisions to


Re: One more thing!!!

Posted by Jessica on August 23, 2001 8:57 AM
I'm sorry it took so long to get back to you. I was off a few days on vacation. I tried it again (with your changes) and it worked!!!! Thanks so much, I really appreciate your help and your patience!!! I should have looked more carefully -- you also


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.