Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between worksheets
Hi guys & gals.
I'm trying to get the below code to work. Basically, it looks down the two columns on both sheets "Bob" and "Jane" and shades diferent rows accordingly. I have two questions 1. How do I define a seperate worksheet for the columns to be shaded in i.e Can I have a worksheet "Results" which has the shading done in it? 2. For Each sheet (Bob, Jane etc) do I have to repeat the macro (as I have below) of is there a way of doing this more efficiently? Many thanks ++++++++++++++++++++++++++++++++++++++++++++++++ Start Code ++++++++++++++++++++++++++++++++++++++++++++++++ Sub Shade_cells() Dim rng As Range Dim c As Range Dim intStart As Integer Dim intNumCells As Integer With Worksheets("Bob") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With For Each c In rng If c.Value < 0 And c.Value < 999 Then intStart = c.Value intNumCells = c.Offset(0, 1).Value c.Offset(0, intStart + c.Column - 1).Resize(1, intNumCells).Interior.ColorIndex = 4 End If Next c Set c = Nothing Set rng = Nothing With Worksheets("Jane") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With For Each c In rng If c.Value < 0 And c.Value < 999 Then intStart = c.Value intNumCells = c.Offset(0, 1).Value c.Offset(0, intStart + c.Column - 1).Resize(1, intNumCells).Interior.ColorIndex = 4 End If Next c Set c = Nothing Set rng = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between worksheets
For Question 2:
Dim ws As Worksheet For Each ws In Sheets(Array("Bob", "Jane")) ...code... Next ws -- Dianne In om, Curious typed: Hi guys & gals. 2. For Each sheet (Bob, Jane etc) do I have to repeat the macro (as I have below) of is there a way of doing this more efficiently? With Worksheets("Bob") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With With Worksheets("Jane") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between worksheets
Sub Shade_cells()
Dim rng As Range Dim c As Range Dim intStart As Integer Dim intNumCells As Integer Dim d as Range, sh1 as Worksheet Dim sh as Worksheet set sh1 = Worksheets("Results") For each sh in Worksheets(Array("Bob","Jane")) With sh Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With For Each c In rng If c.Value < 0 And c.Value < 999 Then intStart = c.Value set d = Sh1.Range(c.Address) intNumCells = c.Offset(0, 1).Value d.Offset(0, intStart + d.Column - 1).Resize(1, _ intNumCells).Interior.ColorIndex = 4 End If Next c Next sh End Sub -- Regards, Tom Ogilvy "Dianne" wrote in message ... For Question 2: Dim ws As Worksheet For Each ws In Sheets(Array("Bob", "Jane")) ...code... Next ws -- Dianne In om, Curious typed: Hi guys & gals. 2. For Each sheet (Bob, Jane etc) do I have to repeat the macro (as I have below) of is there a way of doing this more efficiently? With Worksheets("Bob") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With With Worksheets("Jane") Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row) End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically switching between worksheets. | Excel Discussion (Misc queries) | |||
keyboard shortcut for switching between worksheets?? | Excel Worksheet Functions | |||
Short cut for switching between worksheets | Excel Worksheet Functions | |||
What is the shortcut for switching between worksheets? | Excel Discussion (Misc queries) | |||
Switching to different worksheets during a macro | Excel Programming |