Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on all worksheets within workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on all worksheets within workbook
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 "Marek Socha" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on all worksheets within workbook
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 -- Regards, Tom Ogilvy Marek Socha wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on all worksheets within workbook
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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on all worksheets within workbook
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
Apply Macro to all worksheets in a workbook except one | Excel Discussion (Misc queries) | |||
Macro to paste worksheets in another workbook | Excel Discussion (Misc queries) | |||
Running Same macro in 250 Worksheets in Same Workbook | Excel Worksheet Functions | |||
Applying same macro to all worksheets in workbook | Excel Discussion (Misc queries) |