Excel VBA -- Finding last row in group of columns

[cbeebe]

I have a spreadsheet that gets its data from an Allen Bradley PLC usin
a program called XLReporter by Sytech (www.sytech.com). I am loggin
the cycle times of the operators for stations #1-#5 in columns
through K. The problem I am having is finding the last row that ha
data in it. Here is my current code:

Sub OverTaktHighlight()
For colIndex = 7 to 11
For rwIndex = 3 to 155
If Worksheets("Sheet1").Cells(rwIndex, colIndex)
Worksheets("Sheet1").Cells(3,21).Value Then
Worksheets("Sheet1").Cells(rwIndex, colIndex).Interior.ColorIndex = 6
Worksheets("Sheet1").Cells(rwIndex, colIndex).Font.ColorIndex = 3
End If
Next rwIndex
Next colIndex
End Sub

What this code does is compares the value of every cell to the value o
cell U3 and if the value is greater than U3 it changes the background t
yellow and the text to red. I would like to be able to get away fro
the hard coded "155" because I don't know how much data is going to b
in each sheet.

Also can I replace the Worksheets("Sheet1") with ActiveSheet? Or i
there a way to do this for all sheets in a workbook

 

[Dianne Butterworth]

How about:

Sub OverTaktHighlight()

Dim lngLastRow As Long
Dim lngCol As Long
Dim lngRow As Long
Dim ws As Worksheet
Dim vntMatch As Variant

For Each ws In ActiveWorkbook.Worksheets
lngLastRow = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
vntMatch = ws.Range("U3").Value
For lngCol = 7 To 11
For lngRow = 3 To lngLastRow
With ws.Cells(lngRow, lngCol)
If .Value > vntMatch Then
.Interior.ColorIndex = 6
.Font.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
End If
End With
Next lngRow
Next lngCol
Next ws

End Sub

 

[Tom Ogilvy]

Sub OverTaktHighlight()
For colIndex = 7 to 11
For rwIndex = 3 to Activesheet.cells(rows.count,7).end(xlup).row
If Activesheet.Cells(rwIndex, colIndex) > _
Activesheet.Cells(3,21).Value Then
Activesheet.Cells(rwIndex, colIndex).Interior.ColorIndex = 6
ActiveSheet.Cells(rwIndex, colIndex).Font.ColorIndex = 3
End If
Next rwIndex
Next colIndex
End Sub

 

[cbeebe]

*
For rwIndex = 3 to Activesheet.cells(rows.count,7).end(xlup).row

*

Tom, is this line of code assuming that Column 7 will have the mos
data points in it? If so, that is not the case. I don't know whic
column will have the most data in it, but I only want to perform th
operation on Columns 7-11.

Thanks,

Cha

 

[Tom Ogilvy]

lastrow = Activesheet.UsedRange.rows(activeSheet.UsedRange.rows.count).row

for rwIndex = 3 to lastrow

 

[Don Lloyd]

This is an addendum to the previous two replies.
In the event of some empty cells on the last row, you could use:-

Dim C, RwTemp, BtmRow
For C = 7 To 11
RwTemp = Cells(Rows.Count, C).End(xlUp).Row
If RwTemp > BtmRow Then BtmRow = RwTemp
Next

BtmRow would then be the last row in the range containg data.

Regards,Don

 

[cbeebe]

Excellent, thank you Tom

 

[cbeebe]

How would I go about having this code work on all sheets and not just
the active sheet?