two newby excel programming questions

[Sam]

1) What is the best way to control looping through a sheet to ensure my
loop does not stop too early?
Currently I am using:
Do While ActiveSheet.Cells(r, 1) <> ""
Some one suggested adding a TRIM, however...I could have blank lines
and spaces in cells. This would cause my loop to end before remaining
data is processed.
I want to process until only spaces remain between my current line and
the end of the sheet.... Is this possible?
2)How do I copy a Conditional Format? I have a conditional format that
checks a value and fills the cell with a color if negative. If I
highlight the whole column and enter the format, then the whole column
changes color if one value is negative. I just want to change that one
cell. I can add the format to one cell at a time, but this is not
practical. It would take too long. How do I copy it to other cells?
Thanks!

 

[Roger Govier]

Hi Sam

One way

Sub tester()
Dim lastrow as long, i as long
ActiveSheet.UsedRange
With Cells.SpecialCells(xlCellTypeLastCell)
lastrow = .row
End With
For i =1 to lastrow
...
do your stuff
...
Next
End Sub

With regard to Conditional Format, when you highlight the whole column,
enter your test for negative value, but ensure that the cell reference
used is relative, not absolute e.g.
=A1<0 not $A$1<0

 

[Sam]

I will give it a try. Thank you.