Skipping cells in a formula
 

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

when you use "with c", you don't need to use "c.value", just ".value".
you also don't need "next c", just "next".
you probably should qualify the sheet name, too. that way the code won't address
cells on the wrong sheet.
you can use the goto if you want, i use it sometimes, but it wasn't needed in
the code i posted. i don't think i'd use the number 1 as the goto location,
either.

dim ws as worksheet
set ws = worksheets("Sheet1")
Dim c As Range
For Each c In ws.Cells.Range("A1:A15") 'FP No.1
With c
If .Value = "" Then GoTo 1
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

--


Gary


"NewGuy" wrote in message
...
Hey, guys. Thanks a WHOLE bunch. I've been messing with this for weeks and I
couldn't figure it out.

I actually combined both of your solutions and it works perfectly:

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If c.Value = "" Then GoTo 1
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

Thanks again. I really appreciate it.

- Ethan

"Gary Keramidas" wrote:

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

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?

Skipping cells in a formula
 

Hey, guys. Thanks a WHOLE bunch. I've been messing with this for weeks and I
couldn't figure it out.

I actually combined both of your solutions and it works perfectly:

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If c.Value = "" Then GoTo 1
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

Thanks again. I really appreciate it.

- Ethan

"Gary Keramidas" wrote:

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?

Skipping cells in a formula
 

Hi. Here's an idea instead of using "GoTo."

Sub Demo()
Dim Cell As Range
For Each Cell In Cells.Range("A1:A15").Cells
With Cell
If .Value < vbNullString Then
If .Value < Now() Then
.Interior.Color = vbRed
.Offset(0, 1).ClearContents
Else
.Interior.Color = vbWhite
.Offset(0, 1).Value = 10
End If
End If
End With
Next Cell
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"NewGuy" wrote in message
...
Hey, guys. Thanks a WHOLE bunch. I've been messing with this for weeks and
I
couldn't figure it out.

I actually combined both of your solutions and it works perfectly:

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If c.Value = "" Then GoTo 1
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

Thanks again. I really appreciate it.

- Ethan

"Gary Keramidas" wrote:

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?

Skipping cells in a formula
 

Hi Gary. Thanks. Didn't see it. I'm using Outlook Express, and for the
last year or so, I seem to be missing many posts. Others have reported the
same problem. I still don't have a solution.
In addition, I'll often see a subject " xxx", but not the original posted
question.
I've experimented with other news readers, and they seem to retrieve all the
posts just fine.
It looks to me like an Outlook Express problem.
Very frustrating !! ;(
--
Dana DeLouis


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
already provided the op with that and they chose not to use it.

<snip

Skipping cells in a formula
 

already provided the op with that and they chose not to use it.

--


Gary


"Dana DeLouis" wrote in message
...
Hi. Here's an idea instead of using "GoTo."

Sub Demo()
Dim Cell As Range
For Each Cell In Cells.Range("A1:A15").Cells
With Cell
If .Value < vbNullString Then
If .Value < Now() Then
.Interior.Color = vbRed
.Offset(0, 1).ClearContents
Else
.Interior.Color = vbWhite
.Offset(0, 1).Value = 10
End If
End If
End With
Next Cell
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"NewGuy" wrote in message
...
Hey, guys. Thanks a WHOLE bunch. I've been messing with this for weeks and I
couldn't figure it out.

I actually combined both of your solutions and it works perfectly:

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If c.Value = "" Then GoTo 1
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

Thanks again. I really appreciate it.

- Ethan

"Gary Keramidas" wrote:

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?

Skipping cells in a formula
 

i use oe, too. i see that once in a while, but not as much as you say you're
seeing it. it's usually a server sync problem when that happens.

--


Gary


"Dana DeLouis" wrote in message
...
Hi Gary. Thanks. Didn't see it. I'm using Outlook Express, and for the last
year or so, I seem to be missing many posts. Others have reported the same
problem. I still don't have a solution.
In addition, I'll often see a subject " xxx", but not the original posted
question.
I've experimented with other news readers, and they seem to retrieve all the
posts just fine.
It looks to me like an Outlook Express problem.
Very frustrating !! ;(
--
Dana DeLouis


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
already provided the op with that and they chose not to use it.

<snip