Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formating
I have a calendar and I want to change the color for the cell that are Sat or
Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#2
|
|||
|
|||
=WEEKDAY(A1,2)5
will be true for Sat or Sun -- HTH Bob Phillips "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#3
|
|||
|
|||
formatconditional formatformula is
=weekday(a1)=1 condition 2 =weekday(a1)=7 -- Don Guillett SalesAid Software "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#4
|
|||
|
|||
Here's one interp on what you're after ..
Assume you have sequential dates in A1:G1 say: 17-May-2005 in A1 to ... 23-May-2005 in G1 and you have 4 rows of data below each date which are to be colored together if the dates in A1:G1 are either Sat or Sun Select A1:G5 Click Formatting Conditional Formatting Under Condition 1, make the setting as: Formula is| =OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7) Click the Format button Patterns tab Light Green? OK Click OK at the main dialog You'll see that cells E1:F5 will be colored Light Green (E1:F1 contains the dates 21-May-2005 / 22-May-2005 which are Sat / Sun) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#5
|
|||
|
|||
OK, I have on C1 to C14 the formula =today(), =today()+1, =today()+3 etc.
Where the value is Sat or Sun I want to have the cell and the 10 next cell below to come up yellow. I enter your formulat but it reads: NAME? How can I have excel recognize the weekend on cells c1 to c14 and hightlight the weekend with a different color? Thank you for your help. Sincerely, PaolaAndrea "Bob Phillips" wrote: =WEEKDAY(A1,2)5 will be true for Sat or Sun -- HTH Bob Phillips "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#6
|
|||
|
|||
Max!
=OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7) =WEEKDAY(A$1,2)5 <vbg -- HTH Bob Phillips "Max" wrote in message ... Here's one interp on what you're after .. Assume you have sequential dates in A1:G1 say: 17-May-2005 in A1 to ... 23-May-2005 in G1 and you have 4 rows of data below each date which are to be colored together if the dates in A1:G1 are either Sat or Sun Select A1:G5 Click Formatting Conditional Formatting Under Condition 1, make the setting as: Formula is| =OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7) Click the Format button Patterns tab Light Green? OK Click OK at the main dialog You'll see that cells E1:F5 will be colored Light Green (E1:F1 contains the dates 21-May-2005 / 22-May-2005 which are Sat / Sun) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#7
|
|||
|
|||
The 10 below, that could include 2 weekends, If you mean 10 right, try this
select C1:L14 go into CF add the formula =WEEKDAY($C1,2)5 Also, if you have a language version of Excel, you might need DIASEM instead of WEEKDAY -- HTH Bob Phillips "PaolaAndrea" wrote in message ... OK, I have on C1 to C14 the formula =today(), =today()+1, =today()+3 etc. Where the value is Sat or Sun I want to have the cell and the 10 next cell below to come up yellow. I enter your formulat but it reads: NAME? How can I have excel recognize the weekend on cells c1 to c14 and hightlight the weekend with a different color? Thank you for your help. Sincerely, PaolaAndrea "Bob Phillips" wrote: =WEEKDAY(A1,2)5 will be true for Sat or Sun -- HTH Bob Phillips "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#8
|
|||
|
|||
Thank you.
I understand your formula with the exception of the 2. A$1,2=6 and A$1,2=7 6 is saturday, 7 is sunday, A is the column name, 1 is the cell number, but what about 2? what does mean? Thank you for your help. Sincerely, PaolaAndrea "Max" wrote: Here's one interp on what you're after .. Assume you have sequential dates in A1:G1 say: 17-May-2005 in A1 to ... 23-May-2005 in G1 and you have 4 rows of data below each date which are to be colored together if the dates in A1:G1 are either Sat or Sun Select A1:G5 Click Formatting Conditional Formatting Under Condition 1, make the setting as: Formula is| =OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7) Click the Format button Patterns tab Light Green? OK Click OK at the main dialog You'll see that cells E1:F5 will be colored Light Green (E1:F1 contains the dates 21-May-2005 / 22-May-2005 which are Sat / Sun) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PaolaAndrea" wrote in message ... I have a calendar and I want to change the color for the cell that are Sat or Sun. The date is a formula (=today()) and I want to have sat or sun come up with a different color, not only where the date is but also some lines that go down the line. Please Help. Sincerely, PaolaAndrea |
#10
|
|||
|
|||
"PaolaAndrea" wrote
Thank you. You're welcome ! I understand your formula with the exception of the 2. .. In "WEEKDAY(A$1,2)", the "2" is the Return_type (from Excel's Help) There are 3 choices to use: 1 (or omitted), 2, 3 1 [or omitted] will number 1 (Sunday) through 7 (Saturday). 2 numbers 1 (Monday) through 7 (Sunday). 3 numbers 0 (Monday) through 6 (Sunday). I just prefer using "2" as it seems more "natural" to regard Mon as "1", Tues as "2" ... Sun as "7" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#11
|
|||
|
|||
"Bob Phillips" wrote
=OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7) =WEEKDAY(A$1,2)5 <vbg Urrgh .. you got me there ! Much neater. Thanks. I forgot a week has a max of 7 days & Sat/Sun are consecutive ! <bg -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating Extreme Question | Excel Worksheet Functions | |||
Expanding conditional formating with reference cells changing | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
more than 3 conditional formating in excel | Excel Discussion (Misc queries) | |||
Conditional Formating when result is text | Excel Worksheet Functions |