ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro Continuation (https://www.excelbanter.com/excel-programming/395557-macro-continuation.html)

Beep Beep
Macro Continuation
 
The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

joel
Macro Continuation
 

Sub LessThan1000orBlank()

Lastrow = Range(Rows.Count, "K").End(xlUp).Row

RowCount = 1
Do While RowCount <= Lastrow
If Cells(RowCount, "K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop
End Sub


"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

Dan R.
Macro Continuation
 
Just use conditional formatting.

--
Dan

Beep Beep
Macro Continuation
 
Joel it stops at the first step. Doesn't like the range object????

Lastrow = Range(Rows.Count, "K").End(xlUp).Row

"Joel" wrote:


Sub LessThan1000orBlank()

Lastrow = Range(Rows.Count, "K").End(xlUp).Row

RowCount = 1
Do While RowCount <= Lastrow
If Cells(RowCount, "K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop
End Sub


"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

Beep Beep
Macro Continuation
 
Thanks Dan, I know I could use conditional formatting, however I have around
30 worksheets in the one workbook and I want to us a Ctrl Shift Key in order
to do all 30 sheets and not have to use cf in all the sheets.

Unless we can write a macro to use conditional formatting??????

"Dan R." wrote:

Just use conditional formatting.

--
Dan

joel
Macro Continuation
 
Sub LessThan1000orBlank()

LastRow = Range(Rows.count,"K").end(xlup).row

RowCount = 1
Do while RowCount <= Lastrow
If cells(RowCount,"K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop



"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

joel
Macro Continuation
 
Sorry, this code is tested and works. Forgot to remove SELECTION from you
original code. Had to comment out x1solid. This format doesn't work on my
computer. It may work on yours.

"Joel" wrote:

Sub LessThan1000orBlank()

LastRow = Range(Rows.count,"K").end(xlup).row

RowCount = 1
Do while RowCount <= Lastrow
If cells(RowCount,"K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop



"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

Beep Beep
Macro Continuation
 
Joel, Thanks for your help however I keep getting the following error message
at the beginning LastRow = Range(Rows.count,"K").end(xlup).row

METHOD 'RANGE' OF OBJECT'_GLOBAL'FAILED


"Joel" wrote:

Sorry, this code is tested and works. Forgot to remove SELECTION from you
original code. Had to comment out x1solid. This format doesn't work on my
computer. It may work on yours.

"Joel" wrote:

Sub LessThan1000orBlank()

LastRow = Range(Rows.count,"K").end(xlup).row

RowCount = 1
Do while RowCount <= Lastrow
If cells(RowCount,"K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop



"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

joel
Macro Continuation
 


"Beep Beep" wrote:

Joel, Thanks for your help however I keep getting the following error message
at the beginning LastRow = Range(Rows.count,"K").end(xlup).row

METHOD 'RANGE' OF OBJECT'_GLOBAL'FAILED


"Joel" wrote:

Sorry, this code is tested and works. Forgot to remove SELECTION from you
original code. Had to comment out x1solid. This format doesn't work on my
computer. It may work on yours.

"Joel" wrote:

Sub LessThan1000orBlank()

LastRow = Range(Rows.count,"K").end(xlup).row

RowCount = 1
Do while RowCount <= Lastrow
If cells(RowCount,"K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop



"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

joel
Macro Continuation
 
Sorry again. I thought I fixed this line
from
LastRow = Range(Rows.count,"K").end(xlup).row
to
LastRow = cells(Rows.count,"K").end(xlup).row

"Beep Beep" wrote:

Joel, Thanks for your help however I keep getting the following error message
at the beginning LastRow = Range(Rows.count,"K").end(xlup).row

METHOD 'RANGE' OF OBJECT'_GLOBAL'FAILED


"Joel" wrote:

Sorry, this code is tested and works. Forgot to remove SELECTION from you
original code. Had to comment out x1solid. This format doesn't work on my
computer. It may work on yours.

"Joel" wrote:

Sub LessThan1000orBlank()

LastRow = Range(Rows.count,"K").end(xlup).row

RowCount = 1
Do while RowCount <= Lastrow
If cells(RowCount,"K") < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
RowCount = RowCount + 1

Loop



"Beep Beep" wrote:

The below macro works ok, however it neither color's the cell and also I
would like the macro to continue on after finding one cell with less than
1,000 or blank. Currently it stops at the first cell it finds with this
criteria. There could be multiple cells that fall under this criteria.

Sub LessThan1000orBlank()

Range("K1").Select

Do Until ActiveCell < 1000
If ActiveCell < 1000 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = x1solid
End With

End If
ActiveCell.Offset(1, 0).Select


Loop

End Sub

Dan R.
Macro Continuation
 
On Aug 15, 11:14 am, Beep Beep
wrote:
Thanks Dan, I know I could use conditional formatting, however I have around
30 worksheets in the one workbook and I want to us a Ctrl Shift Key in order
to do all 30 sheets and not have to use cf in all the sheets.

Unless we can write a macro to use conditional formatting??????

Sure can:


Sub LessThan1000orBlank()
Dim i As Integer

For i = 1 To Worksheets.Count
With Sheets(i).Columns("K")
.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="1000"
.FormatConditions(1).Interior _
.ColorIndex = 6
End With
Next i
End Sub


--
Dan


All times are GMT +1. The time now is 02:01 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com