Hi again,

Ok, I have changed the conditional formula as follows.

You did previously say it should be (G22<0) not (G22="") as I am looking
for the ones with outstanding balance to change to Red.

Condition 1 =(TODAY()-A1=2)*(G1<0)*(A1<"")*(TODAY()-A1<=7) to change to
Red

Condition 2 =(TODAY()-A1-1)*(G1="")*(A1<"") to change to Green

So at the moment I have 2nd September highlighted in Red as there is still a
balance outstanding.

Roll on the 9th and I can see if it changes ok.
Fingers crossed, hopefully this will work now.

Many thanks for your patience,
Regards,
vpschas
(uk)

================================================== ==


"Roger Govier" wrote:

Hi

No, not to both, just the Red case.
The Green case is only triggered when cell A1=TODAY(), not on a range of
dates.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Ok will try this, do I need to add the extra test to both Red and
Green
conditions? Next due date is 9th.

Thank you for your help
vpschas
(uk)

"Roger Govier" wrote:

Hi

I'm sorry!!!
I didn't make myself clear, when I said add another condition. I
meant
another test within the function, not another Condition in the
Conditional formatting

=(TODAY()-A1=2)*(G1="")*(A1<"")*(TODAY()-A1)<=7


--
Regards

Roger Govier


"vpschas" wrote in message
...
Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous
dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from
A3
to
other cells in Column A the A3 cell reference in the formula stays
the
same.
I thought this should update automatically unless you include
$'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the
2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward
balance
ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous
formatting
in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

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