Hi,

I'd like to find out how to format an Excel 2002 worksheet to highlight the
current date (in column A) in green and then if it is overdue by more than 2
days to change to red but if the amount has been paid in full (shown in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then when
more than 2 day's have passed from today and F1 is still blank show red,
otherwise white?

If this isn't what you require then please give more detail - e.g. what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if balance due in
Column E is paid in full in Column F, the cell in Column G is blank, if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate that it is
now due.

When todays date is 2 days past the due date in Column A , highlight in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays date is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then when
more than 2 day's have passed from today and F1 is still blank show red,
otherwise white?

If this isn't what you require then please give more detail - e.g. what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has been
paid in full, it may be showing 0 - but the format is set not to display
zero's.

If this is the case, then you would probably have to change the test in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if balance
due in
Column E is paid in full in Column F, the cell in Column G is blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate that
it is
now due.

When todays date is 2 days past the due date in Column A , highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then
when
more than 2 day's have passed from today and F1 is still blank show
red,
otherwise white?

If this isn't what you require then please give more detail - e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue
by more
than 2
days to change to red but if the amount has been paid in full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)