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