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 |
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 |