Target range up until last row

[munkee]

Sub conditional ()

Dim cell As Range


Set Target = Range("J1:J500")

For Each cell In Target
        Select Case cell.Value
            Case "-3", "3"
                cell.Offset(0, 1).Interior.ColorIndex = 3
            Case "-2", "2"
                cell.Offset(0, 1).Interior.ColorIndex = 37
            Case "-1", "1"
                cell.Offset(0, 1).Interior.ColorIndex = 35
            Case "0"
                cell.Offset(0, 1).Interior.ColorIndex = 38
            Case Else
                cell.Offset(0, 1).Interior.ColorIndex = xlNone
        End Select

Next cell

'End conditional formatting

End Sub

I have the following code which i need to edit.

At present the code links to the target range j1:j500

Set Target = Range("J1:J500")

how can i change this to look for the last row in the sheet so the target is then from J1 -> last row. The problem is that some of the rows in J are blank. However the rows in column C will always have data within up until the last row so I assume the number for the last row can come from there but the target still needs to be column J as i am using offsets to populate column K.

[pike]

Hi munkee try

Range("J1", Range("j" & Rows.Count).End(xlUp))

[Jack in the UK]

Sub myLast_CELL_AND_RANGE_J()
'// jiuk - Excel up to 2002 (Xp) only
Set theRANGE = Range("J1", Range("j65536").End(xlUp))

theRANGE.Select

End Sub