I've got a worksheet that lists our clients and the services we provide. The
services we provide are done on a monthly or quarterly basis and each service
is due by a certain date. I've created a formula in VBE that turns any cell
that is past the due date red.
However, there are certain services that some clients do not recieve and so
do not have a due date, and therefore should not be included in this formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and b) if
I add a row (new client) onto the sheet, It messes up all of my ranges and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier way.
Any suggestions?

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of something like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a date that has passed to
whatever color you specify in the formatting. Just apply it to dates, or your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we provide. The
services we provide are done on a monthly or quarterly basis and each service
is due by a certain date. I've created a formula in VBE that turns any cell
that is past the due date red.
However, there are certain services that some clients do not recieve and so
do not have a due date, and therefore should not be included in this formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and b) if
I add a row (new client) onto the sheet, It messes up all of my ranges and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier way.
Any suggestions?

Dear Bernie,
Thanks for getting back to me. This is the formula I currently have (VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells. I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of something like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a date that has passed to
whatever color you specify in the formatting. Just apply it to dates, or your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we provide. The
services we provide are done on a monthly or quarterly basis and each service
is due by a certain date. I've created a formula in VBE that turns any cell
that is past the due date red.
However, there are certain services that some clients do not recieve and so
do not have a due date, and therefore should not be included in this formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and b) if
I add a row (new client) onto the sheet, It messes up all of my ranges and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier way.
Any suggestions?

For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value ="" GoTo 1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
1
Next C


Alan


"The only dumb question is a question left unasked."



"NewGuy" wrote in message
...
Dear Bernie,
Thanks for getting back to me. This is the formula I currently have (VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells. I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of something
like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a
date that has passed to
whatever color you specify in the formatting. Just apply it to dates, or
your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we
provide. The
services we provide are done on a monthly or quarterly basis and each
service
is due by a certain date. I've created a formula in VBE that turns any
cell
that is past the due date red.
However, there are certain services that some clients do not recieve
and so
do not have a due date, and therefore should not be included in this
formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and
b) if
I add a row (new client) onto the sheet, It messes up all of my ranges
and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier
way.
Any suggestions?

you can use the solutions others provided, but i'd change the code to something
like this because selects and activates are rarely needed.


Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub


--


Gary


"NewGuy" wrote in message
...
Dear Bernie,
Thanks for getting back to me. This is the formula I currently have (VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells. I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of something
like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a date
that has passed to
whatever color you specify in the formatting. Just apply it to dates, or
your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we provide.
The
services we provide are done on a monthly or quarterly basis and each
service
is due by a certain date. I've created a formula in VBE that turns any cell
that is past the due date red.
However, there are certain services that some clients do not recieve and so
do not have a due date, and therefore should not be included in this
formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and b)
if
I add a row (new client) onto the sheet, It messes up all of my ranges and
I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier way.
Any suggestions?

I totaly agree, but I don't see a way passed blank cells, as requested,
using your method, which, might I say, is much clearner.

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
If ActiveCell = "" Then GoTo 1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
1
Next c
End Sub

Alan


"The only dumb question is a question left unasked."


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
you can use the solutions others provided, but i'd change the code to
something like this because selects and activates are rarely needed.


Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub


--


Gary


"NewGuy" wrote in message
...
Dear Bernie,
Thanks for getting back to me. This is the formula I currently have
(VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells.
I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of
something like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a
date that has passed to
whatever color you specify in the formatting. Just apply it to dates,
or your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we
provide. The
services we provide are done on a monthly or quarterly basis and each
service
is due by a certain date. I've created a formula in VBE that turns any
cell
that is past the due date red.
However, there are certain services that some clients do not recieve
and so
do not have a due date, and therefore should not be included in this
formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function
and b) if
I add a row (new client) onto the sheet, It messes up all of my ranges
and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier
way.
Any suggestions?

sorry, i thought someone posted a solution for you to try. try this

Sub test()
Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value = "" Then
' do nothing
ElseIf .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub

--


Gary


"Alan" wrote in message
...
I totaly agree, but I don't see a way passed blank cells, as requested, using
your method, which, might I say, is much clearner.

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
If ActiveCell = "" Then GoTo 1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
1
Next c
End Sub

Alan


"The only dumb question is a question left unasked."


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
you can use the solutions others provided, but i'd change the code to
something like this because selects and activates are rarely needed.


Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub


--


Gary


"NewGuy" wrote in message
...
Dear Bernie,
Thanks for getting back to me. This is the formula I currently have (VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells. I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of something
like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with a date
that has passed to
whatever color you specify in the formatting. Just apply it to dates, or
your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we provide.
The
services we provide are done on a monthly or quarterly basis and each
service
is due by a certain date. I've created a formula in VBE that turns any
cell
that is past the due date red.
However, there are certain services that some clients do not recieve and
so
do not have a due date, and therefore should not be included in this
formula.
I've tried using multiple ranges, and not including those cells in the
formula, but this a) creates a very long code for a simple function and
b) if
I add a row (new client) onto the sheet, It messes up all of my ranges
and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an easier
way.
Any suggestions?