Macro Continuation

[Guest]

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

 

[Guest]

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

 

[Dan R.]

Just use conditional formatting.

 

[Guest]

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

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

 

[Guest]

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??????

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Dan R.]

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