Cool, one last thing, apparently this will get 80% and then the person will
be going back and manually shading acceptions to be paid, what code can I run
to have it look at everything and re-calculate ("B31")?

Thanks!

"Tom Ogilvy" wrote:

At the workbook level one of the arguments to the event is a reference to
the sheet that triggered the event.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

so you would use Sh instead of me.

--
Regards,
Tom Ogilvy


"SMac" wrote in message
...
Tom,

I found where I can put your code in the workbook to use for future but
when
I run the code there it doesn't like:

With Me - particular the Me part - I am guessing that when its in the
worksheet it knows that "Me" pertains to the current worksheet where now I
have it in the workbook how do I call out the sheet?

Thanks!

"Tom Ogilvy" wrote:

Events for calculate are found at the worksheet, workbook and
application
level.

See Chip Pearson's page on events:

http://www.cpearson.com/excel/events.htm
http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy

"SMac" wrote in message
...
AWESOME! This works, one more question though, where do I put this
code so
I
can use it for different spreadsheets, is this possible?

Thanks again.

"Tom Ogilvy" wrote:

Private Sub Worksheet_Calculate()
Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With Me
For Each cell In .Range("A1:A30")
If cell.Text < "" Then
If cell.Value < DateValue("2/1/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 3
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("B31").Value = dblSum

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"SMac" wrote in message
...
Thanks Tom, but the first part works but the second half isn't -
the
CountIf.
No errors or debug problems occur just nothing in B31, any
thoughts?

Also I forgot, some cells in Column A are blank how do I tell it
if
Column
A
is blank then Column B has no shading? Can I add an Else or ElseIf
statement
onto the current first If statement?

Thanks again!

"Tom Ogilvy" wrote:

Private Sub Worksheet_Calculate()
on Error goto ErrHandler:
With Me
for each cell in .Range("A1:A30")
If cell.Value < "2/1/2005" Then
cell.offset(0,1).Interior.ColorIndex = 3
End If
Next
Application.EnableEvents = False
if .Range("B31").Formula = "" then
.Range("B31").Formula = _
"=Countif(A1:A30,""<2/1/2005"",B1:B30)"
end if
Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"SMac" wrote in message
...
Column A contains dates, Column B contains Cost ($).
Why I want code is I want it to look at Column A and
everything
less
than
a
set date i.e. 2/10/05 I want it to shade the coordinating cell
in
Column B
red.
After that I want it to now look at all the cells shaded red
and
sum
just
those cells.
From what I read to do the later Conditional Formatting can't
be
used.

I have this simple code to shade which works:
Private Sub Worksheet_Calculate()
With Me
If .Range("A1").Value < "2/1/2005" Then
.Range("B1").Interior.ColorIndex = 3
End If
End With
End Sub

But when I try to change ("A1") to ("A1:A30") I get run-time
error
13,
so
I
figure I am doing something wrong.

If you could provide sample code for both my request I would
be
most
grateful!

Thanks, Stacey

"Bob Phillips" wrote:

What is in each column, data that is, and what is your CF
formula or
condition that determines whether they are shaded or not?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SMac" wrote in message
...
Cost is broken out in chunks by Customer - that's why if I
could
just
look
for shaded cells, code maybe in VBA?

A & J AUTOMATION INC 1/12/2005 $750.00
---------------
A & J AUTOMATION INC $750.00

A. SCHULMAN INC 1/21/2005 $445.50
A. SCHULMAN INC 2/3/2005 $683.10
---------------
A. SCHULMAN INC $1,128.60

A.A. JANSSON INC 2/17/2005 $268.64
---------------
A.A. JANSSON INC $268.64

Thanks!

"Bob Phillips" wrote:

Stacey,

Why don't you do a conditional sum based upon the same
criteria
that
the
CF
is using< such as

=SUMPRODUCT(--(A1:A100<TODAY()))

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"SMac" wrote in message

...
I have a worksheet that has over 3,000 rows and I have
a
COST
column
that
is
shaded when the DATE column is below a certain date
(which
I
used
conditional
formating).

Now what I would like to do is added together all the
shaded
COST
fields -
what do I need to write to accomplish this?

Thanks!
Stacey