Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?

Hope the subject line isn't totally muddy <g>.

I have a spreadsheet that is super. The only trouble is that weekends
aren't differentiated from weekdays in any way.

Since the spreadsheet contains a macro for the user to select the starting
date and then the rest of the spreadsheet is then populated for 3 weeks
following that date, Saturday and Sunday always fall on different cells at
any given time. Also, affected cells may say "Saturday" or "Sunday" but
there is actually only a formula in any of them. The only exception is the
very first cell which is the initial one the macro dumps to. So the first
might read "Friday" if I choose today's date in the prompt box, but all the
rest of the days of the weeks shown display the dates according to
appropriate variations of this formula:

=IF(A2<>"",A2+1,"")

In today's example, this actual formula above displays "Saturday". The cell
immediately below displays "Sep.17.2005". I'm hoping that since it's the
same type formatting issue, hopefully resolving cell colouring for the text
"Saturday" issue will lead me to figure out how to do so for dates that fall
on weekends.

Is there a way to do this cell colour change under these conditions?

I'm in favour of conditional formatting, but couldn't figure out how to do
so because of the fact above that no actual text is found where all the
Saturdays and Sundays are, only the formula. Tx.! :oD

Look at Excel's WEEKDAY function, eg

=WEEKDAY(mydate,3)

If it returns 0-4 it's a weekday. Lends itself nicely to conditional
formatting.

Regards,
Peter T


"StargateFanFromWork" <[email protected]> wrote in message
news:e#[email protected]...
> Hope the subject line isn't totally muddy <g>.
>
> I have a spreadsheet that is super. The only trouble is that weekends
> aren't differentiated from weekdays in any way.
>
> Since the spreadsheet contains a macro for the user to select the starting
> date and then the rest of the spreadsheet is then populated for 3 weeks
> following that date, Saturday and Sunday always fall on different cells at
> any given time. Also, affected cells may say "Saturday" or "Sunday" but
> there is actually only a formula in any of them. The only exception is
the
> very first cell which is the initial one the macro dumps to. So the first
> might read "Friday" if I choose today's date in the prompt box, but all
the
> rest of the days of the weeks shown display the dates according to
> appropriate variations of this formula:
>
> =IF(A2<>"",A2+1,"")
>
> In today's example, this actual formula above displays "Saturday". The
cell
> immediately below displays "Sep.17.2005". I'm hoping that since it's the
> same type formatting issue, hopefully resolving cell colouring for the
text
> "Saturday" issue will lead me to figure out how to do so for dates that
fall
> on weekends.
>
> Is there a way to do this cell colour change under these conditions?
>
> I'm in favour of conditional formatting, but couldn't figure out how to do
> so because of the fact above that no actual text is found where all the
> Saturdays and Sundays are, only the formula. Tx.! :oD
>
>

You mentioned that you already have a macro on this spreadsheet to pick up
the first date. If you were to run the following macro after you had
completed the selection I think it will do what you want.

Public Sub COLOURCELL()

For Each c In Range("workingdays")
c.Select
If Weekday(ActiveCell.Value) = 1 Or Weekday(ActiveCell.Value) = 7 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next c
End Sub

June



"StargateFanFromWork" <[email protected]> wrote in message
news:e%[email protected]...
> Hope the subject line isn't totally muddy <g>.
>
> I have a spreadsheet that is super. The only trouble is that weekends
> aren't differentiated from weekdays in any way.
>
> Since the spreadsheet contains a macro for the user to select the starting
> date and then the rest of the spreadsheet is then populated for 3 weeks
> following that date, Saturday and Sunday always fall on different cells at
> any given time. Also, affected cells may say "Saturday" or "Sunday" but
> there is actually only a formula in any of them. The only exception is
the
> very first cell which is the initial one the macro dumps to. So the first
> might read "Friday" if I choose today's date in the prompt box, but all
the
> rest of the days of the weeks shown display the dates according to
> appropriate variations of this formula:
>
> =IF(A2<>"",A2+1,"")
>
> In today's example, this actual formula above displays "Saturday". The
cell
> immediately below displays "Sep.17.2005". I'm hoping that since it's the
> same type formatting issue, hopefully resolving cell colouring for the
text
> "Saturday" issue will lead me to figure out how to do so for dates that
fall
> on weekends.
>
> Is there a way to do this cell colour change under these conditions?
>
> I'm in favour of conditional formatting, but couldn't figure out how to do
> so because of the fact above that no actual text is found where all the
> Saturdays and Sundays are, only the formula. Tx.! :oD
>
>

This weekday function looked promising but the same trouble may exist as
before: the dates will always vary in any given cell. B2 may have any day
of the week.

When I finally got a working formula - at least, it returns a value - I get
Thursday instead of Saturday.

I modified this: =IF(A2<>"",A2+1,"")

to what would work, which happened to be this:
=IF(A2<>"",WEEKDAY(A2+1,3),"")

And in the cell that is formatted to show date in text format, it says
Thursday instead of Saturday.

Did I do something wrong?


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Look at Excel's WEEKDAY function, eg
>
> =WEEKDAY(mydate,3)
>
> If it returns 0-4 it's a weekday. Lends itself nicely to conditional
> formatting.
>
> Regards,
> Peter T
>
>
> "StargateFanFromWork" <[email protected]> wrote in message
> news:e#[email protected]...
> > Hope the subject line isn't totally muddy <g>.
> >
> > I have a spreadsheet that is super. The only trouble is that weekends
> > aren't differentiated from weekdays in any way.
> >
> > Since the spreadsheet contains a macro for the user to select the
starting
> > date and then the rest of the spreadsheet is then populated for 3 weeks
> > following that date, Saturday and Sunday always fall on different cells
at
> > any given time. Also, affected cells may say "Saturday" or "Sunday" but
> > there is actually only a formula in any of them. The only exception is
> the
> > very first cell which is the initial one the macro dumps to. So the
first
> > might read "Friday" if I choose today's date in the prompt box, but all
> the
> > rest of the days of the weeks shown display the dates according to
> > appropriate variations of this formula:
> >
> > =IF(A2<>"",A2+1,"")
> >
> > In today's example, this actual formula above displays "Saturday". The
> cell
> > immediately below displays "Sep.17.2005". I'm hoping that since it's
the
> > same type formatting issue, hopefully resolving cell colouring for the
> text
> > "Saturday" issue will lead me to figure out how to do so for dates that
> fall
> > on weekends.
> >
> > Is there a way to do this cell colour change under these conditions?
> >
> > I'm in favour of conditional formatting, but couldn't figure out how to
do
> > so because of the fact above that no actual text is found where all the
> > Saturdays and Sundays are, only the formula. Tx.! :oD
> >
> >
>
>

I don't follow where/how your IF function comes into things. But one way or
another it should be possible to use the results of the WEEKDAY function to
CF your cells. Look in help for the second argument in the function. I used
value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in
another formula.

Enter a date in A1, eg Ctrl-semicolon
In B1, =TEXT(A1,"dddd")
Apply CF to B1 like this

Change "Cell value is" to "Formula is"
=WEEKDAY($A1,3)<5
Apply some format

Add condition-2, also "Formula is"
=WEEKDAY($A1,3)>4
apply a format

Select A1:B1
Grab the small selection handle bottom right, drag down. You should see
Sat-Sun with different format to Mon-Fri.

As I said, I don't follow your IF function so you will need to adapt.
Perhaps helper cells with the same formula as above, to return simply
True/false.

Note the row ref in the formula is relative (without a $) to allow you to
copy the formula down.

Regards,
Peter T


"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
> This weekday function looked promising but the same trouble may exist as
> before: the dates will always vary in any given cell. B2 may have any
day
> of the week.
>
> When I finally got a working formula - at least, it returns a value - I
get
> Thursday instead of Saturday.
>
> I modified this: =IF(A2<>"",A2+1,"")
>
> to what would work, which happened to be this:
> =IF(A2<>"",WEEKDAY(A2+1,3),"")
>
> And in the cell that is formatted to show date in text format, it says
> Thursday instead of Saturday.
>
> Did I do something wrong?
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > Look at Excel's WEEKDAY function, eg
> >
> > =WEEKDAY(mydate,3)
> >
> > If it returns 0-4 it's a weekday. Lends itself nicely to conditional
> > formatting.
> >
> > Regards,
> > Peter T
> >
> >
> > "StargateFanFromWork" <[email protected]> wrote in message
> > news:e#[email protected]...
> > > Hope the subject line isn't totally muddy <g>.
> > >
> > > I have a spreadsheet that is super. The only trouble is that weekends
> > > aren't differentiated from weekdays in any way.
> > >
> > > Since the spreadsheet contains a macro for the user to select the
> starting
> > > date and then the rest of the spreadsheet is then populated for 3
weeks
> > > following that date, Saturday and Sunday always fall on different
cells
> at
> > > any given time. Also, affected cells may say "Saturday" or "Sunday"
but
> > > there is actually only a formula in any of them. The only exception
is
> > the
> > > very first cell which is the initial one the macro dumps to. So the
> first
> > > might read "Friday" if I choose today's date in the prompt box, but
all
> > the
> > > rest of the days of the weeks shown display the dates according to
> > > appropriate variations of this formula:
> > >
> > > =IF(A2<>"",A2+1,"")
> > >
> > > In today's example, this actual formula above displays "Saturday".
The
> > cell
> > > immediately below displays "Sep.17.2005". I'm hoping that since it's
> the
> > > same type formatting issue, hopefully resolving cell colouring for the
> > text
> > > "Saturday" issue will lead me to figure out how to do so for dates
that
> > fall
> > > on weekends.
> > >
> > > Is there a way to do this cell colour change under these conditions?
> > >
> > > I'm in favour of conditional formatting, but couldn't figure out how
to
> do
> > > so because of the fact above that no actual text is found where all
the
> > > Saturdays and Sundays are, only the formula. Tx.! :oD
> > >
> > >
> >
> >
>
>

Another possibility -

You say you are returning days as Text. Sat & Sun are the only days that
start with an "S" - right? (in English at least).

so you could change the first formula in my previous example to
=LEFT($B1,1)="S"
assuming day as text is in B1 and the first character in the cell relates to
the day.

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:#[email protected]...
> I don't follow where/how your IF function comes into things. But one way
or
> another it should be possible to use the results of the WEEKDAY function
to
> CF your cells. Look in help for the second argument in the function. I
used
> value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in
> another formula.
>
> Enter a date in A1, eg Ctrl-semicolon
> In B1, =TEXT(A1,"dddd")
> Apply CF to B1 like this
>
> Change "Cell value is" to "Formula is"
> =WEEKDAY($A1,3)<5
> Apply some format
>
> Add condition-2, also "Formula is"
> =WEEKDAY($A1,3)>4
> apply a format
>
> Select A1:B1
> Grab the small selection handle bottom right, drag down. You should see
> Sat-Sun with different format to Mon-Fri.
>
> As I said, I don't follow your IF function so you will need to adapt.
> Perhaps helper cells with the same formula as above, to return simply
> True/false.
>
> Note the row ref in the formula is relative (without a $) to allow you to
> copy the formula down.
>
> Regards,
> Peter T
>
>
> "StargateFanFromWork" <[email protected]> wrote in message
> news:[email protected]...
> > This weekday function looked promising but the same trouble may exist as
> > before: the dates will always vary in any given cell. B2 may have any
> day
> > of the week.
> >
> > When I finally got a working formula - at least, it returns a value - I
> get
> > Thursday instead of Saturday.
> >
> > I modified this: =IF(A2<>"",A2+1,"")
> >
> > to what would work, which happened to be this:
> > =IF(A2<>"",WEEKDAY(A2+1,3),"")
> >
> > And in the cell that is formatted to show date in text format, it says
> > Thursday instead of Saturday.
> >
> > Did I do something wrong?
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > Look at Excel's WEEKDAY function, eg
> > >
> > > =WEEKDAY(mydate,3)
> > >
> > > If it returns 0-4 it's a weekday. Lends itself nicely to conditional
> > > formatting.
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > > "StargateFanFromWork" <[email protected]> wrote in message
> > > news:e#[email protected]...
> > > > Hope the subject line isn't totally muddy <g>.
> > > >
> > > > I have a spreadsheet that is super. The only trouble is that
weekends
> > > > aren't differentiated from weekdays in any way.
> > > >
> > > > Since the spreadsheet contains a macro for the user to select the
> > starting
> > > > date and then the rest of the spreadsheet is then populated for 3
> weeks
> > > > following that date, Saturday and Sunday always fall on different
> cells
> > at
> > > > any given time. Also, affected cells may say "Saturday" or "Sunday"
> but
> > > > there is actually only a formula in any of them. The only exception
> is
> > > the
> > > > very first cell which is the initial one the macro dumps to. So the
> > first
> > > > might read "Friday" if I choose today's date in the prompt box, but
> all
> > > the
> > > > rest of the days of the weeks shown display the dates according to
> > > > appropriate variations of this formula:
> > > >
> > > > =IF(A2<>"",A2+1,"")
> > > >
> > > > In today's example, this actual formula above displays "Saturday".
> The
> > > cell
> > > > immediately below displays "Sep.17.2005". I'm hoping that since
it's
> > the
> > > > same type formatting issue, hopefully resolving cell colouring for
the
> > > text
> > > > "Saturday" issue will lead me to figure out how to do so for dates
> that
> > > fall
> > > > on weekends.
> > > >
> > > > Is there a way to do this cell colour change under these conditions?
> > > >
> > > > I'm in favour of conditional formatting, but couldn't figure out how
> to
> > do
> > > > so because of the fact above that no actual text is found where all
> the
> > > > Saturdays and Sundays are, only the formula. Tx.! :oD
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Okay, but I'm still lost. Where does the "+1" come into affect.
I actually enter one date only and the "+1" in the formula populates
all the rest. <sigh> I'm still basically just a newbie guys. I
still can't make heads or tails out of everything <g>. Yes, I already
did look at the help file but no examples show much flexibility and I
always have trouble figuring out the parameters, or whatever. I
started doing a google search for examples, a common practice for me,
so that I can see code in action. But so far nothing came up that
helps.

(The IF is just to keep cells blank till there is an entry in the cell
a formula is dependent on. It makes it easier to work with
spreadsheets, I find.)

Thanks.

*******************************
On Sat, 17 Sep 2005 14:12:49 +0100, "Peter T" <peter_t@discussions>
wrote:

>I don't follow where/how your IF function comes into things. But one way or
>another it should be possible to use the results of the WEEKDAY function to
>CF your cells. Look in help for the second argument in the function. I used
>value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in
>another formula.
>
>Enter a date in A1, eg Ctrl-semicolon
>In B1, =TEXT(A1,"dddd")
>Apply CF to B1 like this
>
>Change "Cell value is" to "Formula is"
>=WEEKDAY($A1,3)<5
>Apply some format
>
>Add condition-2, also "Formula is"
>=WEEKDAY($A1,3)>4
>apply a format
>
>Select A1:B1
>Grab the small selection handle bottom right, drag down. You should see
>Sat-Sun with different format to Mon-Fri.
>
>As I said, I don't follow your IF function so you will need to adapt.
>Perhaps helper cells with the same formula as above, to return simply
>True/false.
>
>Note the row ref in the formula is relative (without a $) to allow you to
>copy the formula down.
>
>Regards,
>Peter T
>
>
>"StargateFanFromWork" <[email protected]> wrote in message
>news:[email protected]...
>> This weekday function looked promising but the same trouble may exist as
>> before: the dates will always vary in any given cell. B2 may have any
>day
>> of the week.
>>
>> When I finally got a working formula - at least, it returns a value - I
>get
>> Thursday instead of Saturday.
>>
>> I modified this: =IF(A2<>"",A2+1,"")
>>
>> to what would work, which happened to be this:
>> =IF(A2<>"",WEEKDAY(A2+1,3),"")
>>
>> And in the cell that is formatted to show date in text format, it says
>> Thursday instead of Saturday.
>>
>> Did I do something wrong?
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:[email protected]...
>> > Look at Excel's WEEKDAY function, eg
>> >
>> > =WEEKDAY(mydate,3)
>> >
>> > If it returns 0-4 it's a weekday. Lends itself nicely to conditional
>> > formatting.
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>> > "StargateFanFromWork" <[email protected]> wrote in message
>> > news:e#[email protected]...
>> > > Hope the subject line isn't totally muddy <g>.
>> > >
>> > > I have a spreadsheet that is super. The only trouble is that weekends
>> > > aren't differentiated from weekdays in any way.
>> > >
>> > > Since the spreadsheet contains a macro for the user to select the
>> starting
>> > > date and then the rest of the spreadsheet is then populated for 3
>weeks
>> > > following that date, Saturday and Sunday always fall on different
>cells
>> at
>> > > any given time. Also, affected cells may say "Saturday" or "Sunday"
>but
>> > > there is actually only a formula in any of them. The only exception
>is
>> > the
>> > > very first cell which is the initial one the macro dumps to. So the
>> first
>> > > might read "Friday" if I choose today's date in the prompt box, but
>all
>> > the
>> > > rest of the days of the weeks shown display the dates according to
>> > > appropriate variations of this formula:
>> > >
>> > > =IF(A2<>"",A2+1,"")
>> > >
>> > > In today's example, this actual formula above displays "Saturday".
>The
>> > cell
>> > > immediately below displays "Sep.17.2005". I'm hoping that since it's
>> the
>> > > same type formatting issue, hopefully resolving cell colouring for the
>> > text
>> > > "Saturday" issue will lead me to figure out how to do so for dates
>that
>> > fall
>> > > on weekends.
>> > >
>> > > Is there a way to do this cell colour change under these conditions?
>> > >
>> > > I'm in favour of conditional formatting, but couldn't figure out how
>to
>> do
>> > > so because of the fact above that no actual text is found where all
>the
>> > > Saturdays and Sundays are, only the formula. Tx.! :oD
>> > >
>> > >
>> >
>> >
>>
>>
>

Thank you! Yes and I understood that. But my vb skills aren't up to
finetuning this if it doesn't work (and they never do until tweaked,
that's for sure <lol>). Also, I'd definitely want to chain it to the
first macro somehow. Keep it simple is what I need and one button for
this sheet is enough! <g>

If the weekday function works, it'll be easier to use the conditional
formatting. It seems worth pursuing until I find it definitely
doesn't work.

Thanks so much! :oD

******************************
On Fri, 16 Sep 2005 18:59:11 +0100, "June Macleod"
<[email protected]> wrote:

>You mentioned that you already have a macro on this spreadsheet to pick up
>the first date. If you were to run the following macro after you had
>completed the selection I think it will do what you want.
>
>Public Sub COLOURCELL()
>
> For Each c In Range("workingdays")
> c.Select
> If Weekday(ActiveCell.Value) = 1 Or Weekday(ActiveCell.Value) = 7 Then
> With Selection.Interior
> .ColorIndex = 6
> .Pattern = xlSolid
> End With
> End If
> Next c
>End Sub
>
>June
>
>
>
>"StargateFanFromWork" <[email protected]> wrote in message
>news:e%[email protected]...
>> Hope the subject line isn't totally muddy <g>.
>>
>> I have a spreadsheet that is super. The only trouble is that weekends
>> aren't differentiated from weekdays in any way.
>>
>> Since the spreadsheet contains a macro for the user to select the starting
>> date and then the rest of the spreadsheet is then populated for 3 weeks
>> following that date, Saturday and Sunday always fall on different cells at
>> any given time. Also, affected cells may say "Saturday" or "Sunday" but
>> there is actually only a formula in any of them. The only exception is
>the
>> very first cell which is the initial one the macro dumps to. So the first
>> might read "Friday" if I choose today's date in the prompt box, but all
>the
>> rest of the days of the weeks shown display the dates according to
>> appropriate variations of this formula:
>>
>> =IF(A2<>"",A2+1,"")
>>
>> In today's example, this actual formula above displays "Saturday". The
>cell
>> immediately below displays "Sep.17.2005". I'm hoping that since it's the
>> same type formatting issue, hopefully resolving cell colouring for the
>text
>> "Saturday" issue will lead me to figure out how to do so for dates that
>fall
>> on weekends.
>>
>> Is there a way to do this cell colour change under these conditions?
>>
>> I'm in favour of conditional formatting, but couldn't figure out how to do
>> so because of the fact above that no actual text is found where all the
>> Saturdays and Sundays are, only the formula. Tx.! :oD
>>
>>
>

> Okay, but I'm still lost. Where does the "+1" come into affect.

I've no idea !!

Is your problem you only want to apply CF if the your IF formula returns a
data and not return ""

=AND(WEEKDAY($A1,3)>4,$A1<>"")
=AND(WEEKDAY($A1,3)<5,$A1<>"")

If using my "S" test also combine with a similar AND function

Regards,
Peter T

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> Okay, but I'm still lost. Where does the "+1" come into affect.
> I actually enter one date only and the "+1" in the formula populates
> all the rest. <sigh> I'm still basically just a newbie guys. I
> still can't make heads or tails out of everything <g>. Yes, I already
> did look at the help file but no examples show much flexibility and I
> always have trouble figuring out the parameters, or whatever. I
> started doing a google search for examples, a common practice for me,
> so that I can see code in action. But so far nothing came up that
> helps.
>
> (The IF is just to keep cells blank till there is an entry in the cell
> a formula is dependent on. It makes it easier to work with
> spreadsheets, I find.)
>
> Thanks.
>
> *******************************
> On Sat, 17 Sep 2005 14:12:49 +0100, "Peter T" <peter_t@discussions>
> wrote:
>
> >I don't follow where/how your IF function comes into things. But one way
or
> >another it should be possible to use the results of the WEEKDAY function
to
> >CF your cells. Look in help for the second argument in the function. I
used
> >value 3 so that Mon-Fr returns 0-4, making it easy to return True/false
in
> >another formula.
> >
> >Enter a date in A1, eg Ctrl-semicolon
> >In B1, =TEXT(A1,"dddd")
> >Apply CF to B1 like this
> >
> >Change "Cell value is" to "Formula is"
> >=WEEKDAY($A1,3)<5
> >Apply some format
> >
> >Add condition-2, also "Formula is"
> >=WEEKDAY($A1,3)>4
> >apply a format
> >
> >Select A1:B1
> >Grab the small selection handle bottom right, drag down. You should see
> >Sat-Sun with different format to Mon-Fri.
> >
> >As I said, I don't follow your IF function so you will need to adapt.
> >Perhaps helper cells with the same formula as above, to return simply
> >True/false.
> >
> >Note the row ref in the formula is relative (without a $) to allow you to
> >copy the formula down.
> >
> >Regards,
> >Peter T
> >
> >
> >"StargateFanFromWork" <[email protected]> wrote in message
> >news:[email protected]...
> >> This weekday function looked promising but the same trouble may exist
as
> >> before: the dates will always vary in any given cell. B2 may have any
> >day
> >> of the week.
> >>
> >> When I finally got a working formula - at least, it returns a value - I
> >get
> >> Thursday instead of Saturday.
> >>
> >> I modified this: =IF(A2<>"",A2+1,"")
> >>
> >> to what would work, which happened to be this:
> >> =IF(A2<>"",WEEKDAY(A2+1,3),"")
> >>
> >> And in the cell that is formatted to show date in text format, it says
> >> Thursday instead of Saturday.
> >>
> >> Did I do something wrong?
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:[email protected]...
> >> > Look at Excel's WEEKDAY function, eg
> >> >
> >> > =WEEKDAY(mydate,3)
> >> >
> >> > If it returns 0-4 it's a weekday. Lends itself nicely to conditional
> >> > formatting.
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> >
> >> > "StargateFanFromWork" <[email protected]> wrote in message
> >> > news:e#[email protected]...
> >> > > Hope the subject line isn't totally muddy <g>.
> >> > >
> >> > > I have a spreadsheet that is super. The only trouble is that
weekends
> >> > > aren't differentiated from weekdays in any way.
> >> > >
> >> > > Since the spreadsheet contains a macro for the user to select the
> >> starting
> >> > > date and then the rest of the spreadsheet is then populated for 3
> >weeks
> >> > > following that date, Saturday and Sunday always fall on different
> >cells
> >> at
> >> > > any given time. Also, affected cells may say "Saturday" or
"Sunday"
> >but
> >> > > there is actually only a formula in any of them. The only
exception
> >is
> >> > the
> >> > > very first cell which is the initial one the macro dumps to. So
the
> >> first
> >> > > might read "Friday" if I choose today's date in the prompt box, but
> >all
> >> > the
> >> > > rest of the days of the weeks shown display the dates according to
> >> > > appropriate variations of this formula:
> >> > >
> >> > > =IF(A2<>"",A2+1,"")
> >> > >
> >> > > In today's example, this actual formula above displays "Saturday".
> >The
> >> > cell
> >> > > immediately below displays "Sep.17.2005". I'm hoping that since
it's
> >> the
> >> > > same type formatting issue, hopefully resolving cell colouring for
the
> >> > text
> >> > > "Saturday" issue will lead me to figure out how to do so for dates
> >that
> >> > fall
> >> > > on weekends.
> >> > >
> >> > > Is there a way to do this cell colour change under these
conditions?
> >> > >
> >> > > I'm in favour of conditional formatting, but couldn't figure out
how
> >to
> >> do
> >> > > so because of the fact above that no actual text is found where all
> >the
> >> > > Saturdays and Sundays are, only the formula. Tx.! :oD
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
>

On Sat, 17 Sep 2005 15:02:24 +0100, "Peter T" <peter_t@discussions>
wrote:

>> Okay, but I'm still lost. Where does the "+1" come into affect.
>
>I've no idea !!

<lol> Oh my lord, I'm going around in circles and I'm starting to
affect everyone else ...

I'm just not understanding exactly what I'm to do. I know as just a
power user that that happens often, but after wrapping my brain around
all the suggestions, I'm no closer. I think I'm missing something,
something perhaps new that I don't know about.

Fortunately, I found out about a very neat new service called you send
it. I've just "uploaded' the template in question. It's available at
the following link for a limited time, I think it's a few days:
http://s51.yousendit.com/d.aspx?id=0...826G7278BKOLE9
Pls access it, if you would, anyone, to see what I've been trying to
do.

If you do look at it, you'll see a sheet that a macro populates with
user inputing on the starter date, which is neat. The form is
completely blank because of the IF part in each formula.

What would really, really help and what I need is that any cells
falling on the weekend days of Saturday and Sunday, that they change
colour. Let's just say the 2 lighter greys in the available colours,
say. To give an example, if you choose today's date and that's put in
A1 and A2, that A3 changes to the lightest grey and A1 and A2 change
to the 2nd lightest grey (or just 1 grey if that's all that can be
done). But without having a second macro to change the colours. I
just know my limitations and would prefer the colour change be built
right into the cells themselves. Hopefully the conditional formatting
can be worked into that somehow.

Thanks so much! Appreciate everyone's help so far. It's not anyone's
fault I'm not getting.

>Is your problem you only want to apply CF if the your IF formula returns a
>data and not return ""
>
>=AND(WEEKDAY($A1,3)>4,$A1<>"")
>=AND(WEEKDAY($A1,3)<5,$A1<>"")
>
>If using my "S" test also combine with a similar AND function
>
>Regards,
>Peter T
>
>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>news:[email protected]...
>> Okay, but I'm still lost. Where does the "+1" come into affect.
>> I actually enter one date only and the "+1" in the formula populates
>> all the rest. <sigh> I'm still basically just a newbie guys. I
>> still can't make heads or tails out of everything <g>. Yes, I already
>> did look at the help file but no examples show much flexibility and I
>> always have trouble figuring out the parameters, or whatever. I
>> started doing a google search for examples, a common practice for me,
>> so that I can see code in action. But so far nothing came up that
>> helps.
>>
>> (The IF is just to keep cells blank till there is an entry in the cell
>> a formula is dependent on. It makes it easier to work with
>> spreadsheets, I find.)
>>
>> Thanks.
>>
>> *******************************
>> On Sat, 17 Sep 2005 14:12:49 +0100, "Peter T" <peter_t@discussions>
>> wrote:
>>
>> >I don't follow where/how your IF function comes into things. But one way
>or
>> >another it should be possible to use the results of the WEEKDAY function
>to
>> >CF your cells. Look in help for the second argument in the function. I
>used
>> >value 3 so that Mon-Fr returns 0-4, making it easy to return True/false
>in
>> >another formula.
>> >
>> >Enter a date in A1, eg Ctrl-semicolon
>> >In B1, =TEXT(A1,"dddd")
>> >Apply CF to B1 like this
>> >
>> >Change "Cell value is" to "Formula is"
>> >=WEEKDAY($A1,3)<5
>> >Apply some format
>> >
>> >Add condition-2, also "Formula is"
>> >=WEEKDAY($A1,3)>4
>> >apply a format
>> >
>> >Select A1:B1
>> >Grab the small selection handle bottom right, drag down. You should see
>> >Sat-Sun with different format to Mon-Fri.
>> >
>> >As I said, I don't follow your IF function so you will need to adapt.
>> >Perhaps helper cells with the same formula as above, to return simply
>> >True/false.
>> >
>> >Note the row ref in the formula is relative (without a $) to allow you to
>> >copy the formula down.
>> >
>> >Regards,
>> >Peter T

[snip]

OK I got your file and think I follow. We were so nearly there!

First to clarify to others to avoid downloading your file.

A1 contains a day name as a string, that of the date in A2
A2 contains a Date
A3 is an empty cell

A1 & A2 are populated with a macro (before the macro is run the cells are
empty)

A1 & A2, CF with medium grey and A3 with light grey, if A2 is a Sat or Sun
and A2 actually has a date.

This pattern of 3 cells is repeated 3*7 times with incremented dates by use
of an If formulas

------------------
CF's for A1, A2 & A3 will have exactly the same formula using the "Formula
Is" setting:
=AND(WEEKDAY(A2,3)>4,A2<>"")

Format colour for A1 & A2 medium grey, light grey for A3
Copy A1:A3 an pastespecial formats over the entire area, a multiple of 3
rows.

Regards,
Peter T


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> On Sat, 17 Sep 2005 15:02:24 +0100, "Peter T" <peter_t@discussions>
> wrote:
>
> >> Okay, but I'm still lost. Where does the "+1" come into affect.
> >
> >I've no idea !!
>
> <lol> Oh my lord, I'm going around in circles and I'm starting to
> affect everyone else ...
>
> I'm just not understanding exactly what I'm to do. I know as just a
> power user that that happens often, but after wrapping my brain around
> all the suggestions, I'm no closer. I think I'm missing something,
> something perhaps new that I don't know about.
>
> Fortunately, I found out about a very neat new service called you send
> it. I've just "uploaded' the template in question. It's available at
> the following link for a limited time, I think it's a few days:
> http://s51.yousendit.com/d.aspx?id=0...826G7278BKOLE9
> Pls access it, if you would, anyone, to see what I've been trying to
> do.
>
> If you do look at it, you'll see a sheet that a macro populates with
> user inputing on the starter date, which is neat. The form is
> completely blank because of the IF part in each formula.
>
> What would really, really help and what I need is that any cells
> falling on the weekend days of Saturday and Sunday, that they change
> colour. Let's just say the 2 lighter greys in the available colours,
> say. To give an example, if you choose today's date and that's put in
> A1 and A2, that A3 changes to the lightest grey and A1 and A2 change
> to the 2nd lightest grey (or just 1 grey if that's all that can be
> done). But without having a second macro to change the colours. I
> just know my limitations and would prefer the colour change be built
> right into the cells themselves. Hopefully the conditional formatting
> can be worked into that somehow.
>
> Thanks so much! Appreciate everyone's help so far. It's not anyone's
> fault I'm not getting.
>
> >Is your problem you only want to apply CF if the your IF formula returns
a
> >data and not return ""
> >
> >=AND(WEEKDAY($A1,3)>4,$A1<>"")
> >=AND(WEEKDAY($A1,3)<5,$A1<>"")
> >
> >If using my "S" test also combine with a similar AND function
> >
> >Regards,
> >Peter T
> >
> >"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> >news:[email protected]...
> >> Okay, but I'm still lost. Where does the "+1" come into affect.
> >> I actually enter one date only and the "+1" in the formula populates
> >> all the rest. <sigh> I'm still basically just a newbie guys. I
> >> still can't make heads or tails out of everything <g>. Yes, I already
> >> did look at the help file but no examples show much flexibility and I
> >> always have trouble figuring out the parameters, or whatever. I
> >> started doing a google search for examples, a common practice for me,
> >> so that I can see code in action. But so far nothing came up that
> >> helps.
> >>
> >> (The IF is just to keep cells blank till there is an entry in the cell
> >> a formula is dependent on. It makes it easier to work with
> >> spreadsheets, I find.)
> >>
> >> Thanks.
> >>
> >> *******************************
> >> On Sat, 17 Sep 2005 14:12:49 +0100, "Peter T" <peter_t@discussions>
> >> wrote:
> >>
> >> >I don't follow where/how your IF function comes into things. But one
way
> >or
> >> >another it should be possible to use the results of the WEEKDAY
function
> >to
> >> >CF your cells. Look in help for the second argument in the function. I
> >used
> >> >value 3 so that Mon-Fr returns 0-4, making it easy to return
True/false
> >in
> >> >another formula.
> >> >
> >> >Enter a date in A1, eg Ctrl-semicolon
> >> >In B1, =TEXT(A1,"dddd")
> >> >Apply CF to B1 like this
> >> >
> >> >Change "Cell value is" to "Formula is"
> >> >=WEEKDAY($A1,3)<5
> >> >Apply some format
> >> >
> >> >Add condition-2, also "Formula is"
> >> >=WEEKDAY($A1,3)>4
> >> >apply a format
> >> >
> >> >Select A1:B1
> >> >Grab the small selection handle bottom right, drag down. You should
see
> >> >Sat-Sun with different format to Mon-Fri.
> >> >
> >> >As I said, I don't follow your IF function so you will need to adapt.
> >> >Perhaps helper cells with the same formula as above, to return simply
> >> >True/false.
> >> >
> >> >Note the row ref in the formula is relative (without a $) to allow you
to
> >> >copy the formula down.
> >> >
> >> >Regards,
> >> >Peter T
>
> [snip]
>

On Sun, 18 Sep 2005 17:02:09 +0100, "Peter T" <peter_t@discussions>
wrote:

>OK I got your file and think I follow. We were so nearly there!
>
>First to clarify to others to avoid downloading your file.
>
>A1 contains a day name as a string, that of the date in A2
>A2 contains a Date
>A3 is an empty cell
>
>A1 & A2 are populated with a macro (before the macro is run the cells are
>empty)
>
>A1 & A2, CF with medium grey and A3 with light grey, if A2 is a Sat or Sun
>and A2 actually has a date.
>
>This pattern of 3 cells is repeated 3*7 times with incremented dates by use
>of an If formulas
>
>------------------
>CF's for A1, A2 & A3 will have exactly the same formula using the "Formula
>Is" setting:
>=AND(WEEKDAY(A2,3)>4,A2<>"")
>
>Format colour for A1 & A2 medium grey, light grey for A3
>Copy A1:A3 an pastespecial formats over the entire area, a multiple of 3
>rows.
>
>Regards,
>Peter T

Thank you. I will look at this tonight. At least you're still saying
it's do-able! <lol>