| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Continuation
Just use conditional formatting.
-- Dan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Continuation of a macro | Excel Discussion (Misc queries) | |||
| Line Continuation issue | Excel Worksheet Functions | |||
| formula continuation with the paint too. | New Users to Excel | |||
| continuation from yesterday | Excel Discussion (Misc queries) | |||
| Continuation of the listbox question | Excel Programming | |||
Linear Mode
