VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

If you provide the formula which drives the conditional formatting of the
COST column I can probably help.

"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

VBA Code to sum shaded cells
 

try:-

= countif(a1:a10,<1/10/05,b1:b10)

Assumunig a1:a10 = date column
b1:b10 = cost column


"Andibevan" wrote in message
...
If you provide the formula which drives the conditional formatting of the
COST column I can probably help.

"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

VBA Code to sum shaded cells
 

Stacey,

Perhaps the easiest way would be to include a column of helper cells, each
with an IF condition that replicates the formula you have in you conditional
formatting, and the value to be summed if true.
eg
=IF(A2$A$1,B2,0)
where A2 is a date to be compared with a fixed date in $A1$ and B2 to be
summed. Copy the formula down and sum the helper column.
Or, look into SumIf or Sumproduct for a single formula solution.

Regards,
Peter T

"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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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

VBA Code to sum shaded cells
 

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