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

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

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