Formating Depending On If Statement (2000)

Topic

New Reply

Row A1:AD1 contains days of the week in the format “dd-mmmm-yyyy” (i. e., “03-July-2002”). Row A2:AD2 contains the corresponding day of the week of the date in the cell immediately above the weekday cell (i. e., cell A2 contains “Monday”, inasmuch as cell A1 contains “01-July-2002”). How can I cause the (1) the formatting in cell A3 to be (a) shaded, or ( contain a certain type of border (i. e., an “X”), or, (2) the contents of cell A3 to return “N/A”, when cell A2 is a Saturday or Sunday?
Thanks,
Jeff

Reply | Quote

Replies

You can use conditional formatting for (1) (a) or (.
Select cell A3.
Select Format/Conditional Formatting…
In the first box, select Formula
In the second box, type =OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
Click the Format… button
Set the formatting options you like (Font, Border, Shading)
Click OK twice.
Fill right to AD3.

For (2), use a formula
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),”N/A”,””)

Reply | Quote

I still can’t get an “X” pattern of borders (i. e., 2 crossing lines-one from the top left corner of a cell to the bottom right corner, and the second from the top right corner of a cell to the bottom left corner) with any of the suggested solutions! What am I doing wrong?
Thanks,
Jeff

Reply | Quote

I hadn’t noticed it before, but now that you point it out: the diagonal border options are greyed out in the format dialog for conditional formatting. I guess Microsoft hasn’t implemented this in 97/2000 (perhaps in XP?) As an alternative, you can use shading, with a solid color or a pattern.

Reply | Quote

Nope, still greyed out in XP.

Reply | Quote

Hi Jeff,

select A3:AD3 and do menu command Format/Conditional Formatting.

In Condition 1 box select Formula Is, and in the next box type …
=MOD(WEEKDAY(A1),6)=1
and click the Format… button to choose a pattern for shading Sats and Suns. Press OK, press OK.

That should do it.

Glenn.

Reply | Quote