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

--
Message posted from http://www.ExcelForum.com

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

--
Dianne Butterworth


I have a spreadsheet that gets its data from an Allen Bradley PLC
using a program called XLReporter by Sytech (www.sytech.com). I am
logging the cycle times of the operators for stations #1-#5 in
columns G through K. The problem I am having is finding the last row
that has 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
of cell U3 and if the value is greater than U3 it changes the
background to yellow and the text to red. I would like to be able to
get away from the hard coded "155" because I don't know how much data
is going to be in each sheet.

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


---
Message posted from http://www.ExcelForum.com/

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

--
regards,
Tom Ogilvy

"cbeebe " wrote in message
...
I have a spreadsheet that gets its data from an Allen Bradley PLC using
a program called XLReporter by Sytech (www.sytech.com). I am logging
the cycle times of the operators for stations #1-#5 in columns G
through K. The problem I am having is finding the last row that has
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 of
cell U3 and if the value is greater than U3 it changes the background to
yellow and the text to red. I would like to be able to get away from
the hard coded "155" because I don't know how much data is going to be
in each sheet.

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


---
Message posted from http://www.ExcelForum.com/

Tom Ogilvy wrote:
*
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

--
Message posted from http://www.ExcelForum.com

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

for rwIndex = 3 to lastrow

--
Regards,
Tom Ogilvy


"cbeebe " wrote in message
...
Tom Ogilvy wrote:
*
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 most
data points in it? If so, that is not the case. I don't know which
column will have the most data in it, but I only want to perform the
operation on Columns 7-11.

Thanks,

Chad


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
I have a spreadsheet that gets its data from an Allen Bradley PLC using
a program called XLReporter by Sytech (www.sytech.com). I am logging
the cycle times of the operators for stations #1-#5 in columns G
through K. The problem I am having is finding the last row that has
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 of
cell U3 and if the value is greater than U3 it changes the background to
yellow and the text to red. I would like to be able to get away from
the hard coded "155" because I don't know how much data is going to be
in each sheet.

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


---
Message posted from http://www.ExcelForum.com/

Excellent, thank you Tom

--
Message posted from http://www.ExcelForum.com

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


---
Message posted from http://www.ExcelForum.com/