OK that worked except it found all the columns with data
in and resized the rows to 1 and turned them blank


Dim lastrow As Range
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range(Cells(2, 5), Cells(lastrow, "B"))
For Each oCell In Rng

If oCell < 0 Then
oCell.RowHeight = 12.57
Else
oCell.RowHeight = 1
oCell.EntireRow.Interior.ColorIndex = 15
End If
Next oCell
-----Original Message-----
Sorry, that should just be

lastrow = Cells(Rows.Count,"B").End(xlUp).Row

no Set.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel BEnnett" wrote in message
...
It doesn't like the

set lastrow line,

comes back with object required
-----Original Message-----
Set lastrow = Cells(Rows.Count,"B").End(xlUp).Row
Set Rng = Range(Cells(2, 5), Cells(lastrow,"B"))
For Each oCell In Rng

If oCell < 0 Then
oCell.RowHeight = 12.57
Else
oCell.RowHeight = 1
oCell.EntireRow.Interior.ColorIndex = 15
End If
Next oCell

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"Nigel Bennett" wrote in message
...
I had got some help and recived code that checked
for a
blank column and if it found the column it would
color
it,

I tried modifying the code to find a blank cell in a
row
and then format that row

Here is the Code

Set lastrow = Cells(2, Rows.Count).End(xlTopToBottom)
Set Rng = Range(Cells(2, 5), lastrow)
For Each oCell In Rng

If oCell < 0 Then
oCell.RowHeight = 12.57
Else
oCell.RowHeight = 1
oCell.EntireRow.Interior.ColorIndex = 15
End If
Next oCell

if I set the range to range("b5:B200) then every row
after
the last row of data is resized, I was trying to set
the
last row to find the last row with data so it would
stop
there but it failed


Any Ideas

THanks

Nigel


.



.