Pls, I need to monitor cheques that are post dated and cash that is supposed
to be deposited into the bank next 1 or 2 days.
Thank U in anticipation.

If I were going to do my banking in such a manner, I'd first add a column
where I could also indicate that the needed deposit has been made.

Assuming that the date for the check is in column B and the "deposit has
been made" indicator column is C then a formula like this would flag upcoming
due checks when it is 3 days from its due date and ignore any that are due
farther out than that or that have passed their due date. Example is for row
2:
=IF(AND(B2NOW()-4,B2<=NOW(),C2=""),"MAKE DEPOSIT","")
Any entry in column C such as an X or any non-blank character will mark it
as 'deposit made'.

"Saeed" wrote:

Pls, I need to monitor cheques that are post dated and cash that is supposed
to be deposited into the bank next 1 or 2 days.
Thank U in anticipation.

I thank you JLatham for the swift response. I tried out the formula but I
couldn't get what I needed may be you would get the picture of my needs
through this info :

Main Cash

Date
Description
Dr
Cr
Balance

9-Nov
Abc
941,000
-
941,000

9-Nov
123
35,000
-
976,000

9-Nov
Romeo
11,000
-
987,000

10-Nov
London Bank
-
941,000
46,000

10-Nov
International Bank
-
11,000
35,000


This sheet is a very long list. Sometimes a daily debits may amount to 30
entries or more.Every debit is expected to be moved(Credited) the next day.

So, I need those Debits(35,000) that are yet to be moved to the banks to be
HIGHLIGHTED(Due Date).

"Saeed" wrote in message
...
Pls, I need to monitor cheques that are post dated and cash that is
supposed to be deposited into the bank next 1 or 2 days.
Thank U in anticipation.

With 35,000 entries to check, and with what I would think is a very
dynamic/changing list on the sheet, I believe we are better off with a Macro
to do the job. There is no real maintenance to do with a Macro, where with a
formula or conditional formatting, you'd constantly be 'maintaining' the
worksheet itself instead of focusing on the job at hand.

What I've come up with is a macro that will run at 2 different times:
First - any time you choose the sheet with the entries on it, it will run
automatically.
Second - the routine may also be run 'on demand' through the Tools | Macro |
Macros menu.

I've actually coded this 3 different ways and you can see how each is set up
and choose the one most appropriate to your workbook. My email address is
provided in each of these workbooks.

#1 - entries are all in a single column as you showed them in your example.
The code for this method is provided below also. A workbook with that set up
can be downloaded from:
http://www.jlathamsite.com/uploads/S...mnSolution.xls

#2 - entries are all on one sheet and laid out in column fashion, but you
have several groups in different columns on the sheet. An example of that
solution using 2 groups is provided in this workbook:
http://www.jlathamsite.com/uploads/S...mnSolution.xls

#3 - the entries are actually on individual rows running down the sheet as:
Date Dr Cr Balance
and a solution for that situation is provided in this workbook:
http://www.jlathamsite.com/uploads/S...wsSolution.xls

Now, here is the code for the #1/first solution - single column. We begin
with some code for the worksheet's Activate event. To put it into the proper
place, go to the worksheet and right-click on the name tab and choose [View
Code]. Then copy this code and paste it into the module. Close the Visual
Basic Editor (VBE).
Private Sub Worksheet_Activate()
Run "ChecksToMove"
End Sub

Next comes the code that performs the work. To put it into the correct
location, press [Alt]+[F11] to open the VBE and choose Insert | Module and
then copy the code below and paste it into that module. Edit the Const
values as needed for your workbook - hopefully the explanation of what each
is for is clear.

Sub ChecksToMove()
'redefine these for your worksheet
Const firstDateRow = 6 ' row first date entry is on
Const entryColumn = "B" ' column that entries are in
Const rowsToNextDate = 5 ' rows between date entries
Const rowsToCredit = 2 ' how many rows from date to Cr entry
Const warnColor = 6 ' YELLOW
Const overdueColor = 3 ' RED
Const daysLead = 3 ' get warning 2 days ahead

Dim baseCell As Range
Dim rOffset As Long
Dim lastRow As Long
Dim firstDate As Date
Dim lastDate As Date

'determine how far down the sheet we have to work
lastRow = Range(entryColumn & Rows.Count).End(xlUp).Row
If lastRow <= firstDateRow Then
Exit Sub ' no work to do
End If
Set baseCell = Range(entryColumn & firstDateRow)
'set up range of dates to test against
firstDate = DateSerial(Year(Now()), Month(Now()), Day(Now()))
lastDate = DateSerial(Year(Now()), Month(Now()), Day(Now()) + 3)
Do Until (baseCell.Row + rOffset) lastRow
'clear any previous flag on this cell
baseCell.Offset(rOffset, 0).Interior.ColorIndex = xlNone
'test if date is within 2 days of today and
'whether or not credit has been applied
If baseCell.Offset(rOffset, 0) = firstDate And _
baseCell.Offset(rOffset, 0) <= lastDate And _
baseCell.Offset(rOffset + rowsToCredit, 0) <= 0 Then
'within 2 days, no credit applied, flag it
baseCell.Offset(rOffset, 0).Interior.ColorIndex = warnColor
End If
'test if date has passed and nothing was done
If baseCell.Offset(rOffset, 0) < firstDate And _
baseCell.Offset(rOffset + rowsToCredit) <= 0 Then
'we missed moving the money!
baseCell.Offset(rOffset, 0).Interior.ColorIndex = overdueColor
End If
'move to next group
rOffset = rOffset + rowsToNextDate
Loop
End Sub



"Saeed" wrote:

I thank you JLatham for the swift response. I tried out the formula but I
couldn't get what I needed may be you would get the picture of my needs
through this info :

Main Cash

Date
Description
Dr
Cr
Balance

9-Nov
Abc
941,000
-
941,000

9-Nov
123
35,000
-
976,000

9-Nov
Romeo
11,000
-
987,000

10-Nov
London Bank
-
941,000
46,000

10-Nov
International Bank
-
11,000
35,000


This sheet is a very long list. Sometimes a daily debits may amount to 30
entries or more.Every debit is expected to be moved(Credited) the next day.

So, I need those Debits(35,000) that are yet to be moved to the banks to be
HIGHLIGHTED(Due Date).

"Saeed" wrote in message
...
Pls, I need to monitor cheques that are post dated and cash that is
supposed to be deposited into the bank next 1 or 2 days.
Thank U in anticipation.