Run macro on all worksheets within workbook

[Marek Socha]

I have a simple macro that highlights entire row based on specific
value within specified range.


Sub RowHighlight()
Dim DataRng As Range
Dim LastRow As Long
Dim cell As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A3:A7")
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next cell
End Sub

My workbook has more than a 100 worksheets...it would be a pain to tab
through all of them and run this macro.

How do I make it run through all worksheets?

Any help would be greatly appreciated.

Thanks,

Mark

 

[Jim Cone]

Mark,

See following code.
All references to "LastRow" were removed.
Adding some error handling would be a good idea.

'----------------------------------------------------
Sub RowHighlight()
Dim WS As Worksheet
Dim DataRng As Range
Dim cell As Range

For Each WS In Worksheets
Set DataRng = WS.Range("A3:A7")
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next ' cell
Next 'WS

Set cell = Nothing
Set DataRng = Nothing
Set WS = Nothing
End Sub
'--------------------------------------------

Regards,
Jim Cone
San Francisco, CA

I have a simple macro that highlights entire row based on specific
value within specified range.
Sub RowHighlight()
Dim DataRng As Range
Dim LastRow As Long
Dim cell As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A3:A7")
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next cell
End Sub
My workbook has more than a 100 worksheets...it would be a pain to tab

through all of them and run this macro.

 

[Tom Ogilvy]

Sub RowHighlight()
Dim DataRng As Range
Dim LastRow As Long
Dim cell As Range
Dim sh as Worksheet
'LastRow = Cells(Rows.Count, "A").End(xlUp).Row
for each sh in ActiveWorkbook.worksheets
sh.Activate
Set DataRng = Range("A3:A7")
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next cell
Next
End Sub

You never use LastRow. Do you want the range determined by Lastrow

Sub RowHighlight()
Dim DataRng As Range
Dim LastRow As Long
Dim cell As Range
Dim sh as Worksheet
for each sh in ActiveWorkbook.worksheets
sh.Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A3:A" & LastRow)
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next cell
Next
End Sub

 

[Patrick Molly]

I see some good code replies. However, all you're doing
is setting a row's color to red if the cell in A contains
the text " Date"
Seems to me that all yuo need to do is set a conditional
format on all the sheets.
First select all the sheets. Make sure the tabs are
visible, slect the first sheet, hold down shift & select
the last sheet.
Now slecty all the cells by clicking the 'cell' above
the '1' row and left of the 'A' column. Click
Format/Conditional Formatting , change to Formula Is and
enter
=($A1=" Date")
set the format to the Patter required.
Note that A1 must be the 'active' cell.

Any row in any sheet where the cell in column A is '
Date' will be red.

Patrick Molloy

 

[Marek Socha]

I have a simple macro that highlights entire row based on specific
value within specified range.


Sub RowHighlight()
Dim DataRng As Range
Dim LastRow As Long
Dim cell As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A3:A7")
For Each cell In DataRng
If cell.Value = " Date" Then
cell.EntireRow.Interior.ColorIndex = 3
End If
Next cell
End Sub

My workbook has more than a 100 worksheets...it would be a pain to tab
through all of them and run this macro.

How do I make it run through all worksheets?

Any help would be greatly appreciated.

Thanks,

Mark

Thanks guys...works exactly the way I wanted it to...this group is just fantastic

Mark