looping through columns

[stumpy]

I am trying to check each column of a worksheet for the last row in each
column, then highlight that cell if it is below a set value.
The column and row count can be different every time data is imported.
using
CountRows=Range("C" & Rows.Count).End(xlUp).Select
If ActiveCell >0 then
If ActiveCell<5 then
ActiveCell.BorderAround _
ColorIndex:=3,Weight:= xlthick
End If
End If

This allows me to highlight the last cell in column c if the value is
between 0 and 5.
Is there any way to do this for all columns in a worksheet.
Forgive my serious lack of experience, any help is greatly appreciated

Rob

 

[Per Jessen]

Hi

Try this, just notice cells with the value 0 and 5 will not be highlighted.

Sub aaa()
Dim LastColCell As Range
FirstCol = 1
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For col = FirstCol To LastCol
Set LastColCell = Cells(Rows.Count, col).End(xlUp)
If LastColCell > 0 And LastColCell < 5 Then
LastColCell.BorderAround _
ColorIndex:=3, Weight:=xlThick
End If
Next
End Sub

Regards,
Per

 

[Luke M]

'I believe this will work for you.

Sub ChangeBorder()
'Prevent screen flashing
Application.ScreenUpdating = False

'Standard XL sheet has 256 columns
For i = 1 To 256
CountRows = Cells(Rows.Count, i).End(xlUp).Select

If ActiveCell > 0 And ActiveCell < 5 Then
ActiveCell.BorderAround _
ColorIndex:=3, Weight:=xlThick
End If

Next

'Return to begining of sheet
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[stumpy]

Thanks for all the help worked perfectly.

Regards Rob