Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance, not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted the
conditional formatting into all cells in Column A but now only all the
previous dates where the balance outstanding in Column G is zero are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values greater
than
2 and empty cells would all be made Red by the conditional formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date 2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are
still
an
outstanding balance to pay in G22 but the date is not highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is
greater
than 2 or greater than -1, you've already said that G22=0 is to
check
if zero
balance in G22 but can you please explain the last part of the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

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)