If blank cell continue macro
 

I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if the
above occurs.

I actually want it to run until it gets to the bottom of the column and hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

Test this one (untested)

It will loop through A1 till the last cell with data in A
If the value in the cell is a date it do your stuff

Sub test()
Dim lr As Long
Dim cell As Range

With ActiveSheet
lr = .Cells("A", Rows.Count).End(xlUp).Row

For Each cell In .Range("A1:A" & lr)
If IsDate(cell) Then

If Now - cell.Value 30 Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value Now Then
cell.Interior.ColorIndex = 34
End If

End If
Next cell
End With

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Beep Beep" wrote in message ...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if the
above occurs.

I actually want it to run until it gets to the bottom of the column and hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

One way, depending on what you want with blanks & "?"
Sub changecolorif()
lr = Cells(Rows.Count, "e").End(xlUp).Row
For Each c In Range("e12:e" & lr)
c.Interior.ColorIndex = xlNone
If Date - c 30 Then x = 3
If c Date Then x = 34
c.Interior.ColorIndex = x
Next c
End Sub

--
Don Guillett
SalesAid Software

"Beep Beep" wrote in message
...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if
the
above occurs.

I actually want it to run until it gets to the bottom of the column and
hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

Oops

must be
lr = .Cells(Rows.Count, "A").End(xlUp).Row

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Test this one (untested)

It will loop through A1 till the last cell with data in A
If the value in the cell is a date it do your stuff

Sub test()
Dim lr As Long
Dim cell As Range

With ActiveSheet
lr = .Cells("A", Rows.Count).End(xlUp).Row

For Each cell In .Range("A1:A" & lr)
If IsDate(cell) Then

If Now - cell.Value 30 Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value Now Then
cell.Interior.ColorIndex = 34
End If

End If
Next cell
End With

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Beep Beep" wrote in message ...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if the
above occurs.

I actually want it to run until it gets to the bottom of the column and hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

Thanks Guillett

It stops at the first blank. Any suggestions. I want it to continue on and
stop at the last cell (next cell would be blank)

Frank

"Don Guillett" wrote:

One way, depending on what you want with blanks & "?"
Sub changecolorif()
lr = Cells(Rows.Count, "e").End(xlUp).Row
For Each c In Range("e12:e" & lr)
c.Interior.ColorIndex = xlNone
If Date - c 30 Then x = 3
If c Date Then x = 34
c.Interior.ColorIndex = x
Next c
End Sub

--
Don Guillett
SalesAid Software

"Beep Beep" wrote in message
...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if
the
above occurs.

I actually want it to run until it gets to the bottom of the column and
hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

Ron YOU ARE THE MAN

Perfect

Thanks
Frank

"Ron de Bruin" wrote:

Oops

must be
lr = .Cells(Rows.Count, "A").End(xlUp).Row

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Test this one (untested)

It will loop through A1 till the last cell with data in A
If the value in the cell is a date it do your stuff

Sub test()
Dim lr As Long
Dim cell As Range

With ActiveSheet
lr = .Cells("A", Rows.Count).End(xlUp).Row

For Each cell In .Range("A1:A" & lr)
If IsDate(cell) Then

If Now - cell.Value 30 Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value Now Then
cell.Interior.ColorIndex = 34
End If

End If
Next cell
End With

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Beep Beep" wrote in message ...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a cell
with a question mark (?) in it. How can I change this macro to bypass if the
above occurs.

I actually want it to run until it gets to the bottom of the column and hits
a blank cell there and then stops.

Thanks
Frank

If blank cell continue macro
 

I just RE-tested and found NO problem. Send your workbook if desired.
--
Don Guillett
SalesAid Software

"Beep Beep" wrote in message
...
Thanks Guillett

It stops at the first blank. Any suggestions. I want it to continue on
and
stop at the last cell (next cell would be blank)

Frank

"Don Guillett" wrote:

One way, depending on what you want with blanks & "?"
Sub changecolorif()
lr = Cells(Rows.Count, "e").End(xlUp).Row
For Each c In Range("e12:e" & lr)
c.Interior.ColorIndex = xlNone
If Date - c 30 Then x = 3
If c Date Then x = 34
c.Interior.ColorIndex = x
Next c
End Sub

--
Don Guillett
SalesAid Software

"Beep Beep" wrote in message
...
I have a column with dates in it and I am running the following macro:

Sub colr()


Range("e2").Select
Do
If Now - ActiveCell.Value 30 Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value Now Then
ActiveCell.Interior.ColorIndex = 34
End If
ActiveCell.Offset(1, 0).Select
If ActiveCell = "" Then
Exit Do
End If
Loop Until ActiveCell.Value = " "
End Sub

Macro runs fine, however it stops when it comes to a blank cell or a
cell
with a question mark (?) in it. How can I change this macro to bypass
if
the
above occurs.

I actually want it to run until it gets to the bottom of the column and
hits
a blank cell there and then stops.

Thanks
Frank