Hi list,

Can someone please help me with a simple macro to do the following
calender-style function?

I have two columns

Start Length
3 5

I need a quick and dirty macro to shade the rows to the left. If
Start=3 then the first column to be shaded should be 3 columns away
from the start and (with length=5) the number of cells to be shaded
should be 5.

To explain better (I hope!!). Assume that we start with a1=Start,
a2=3, b1-length and b2=5 I would want cells e2-i2 shaded.

Does that make it any clearer?

Thanks in advance

Assuming your Start is in column A. If not, change the A's in the set
rng code to whatever your column letter is:

Sub ShadeCells()

Dim rng As Range
Dim c As Range
Dim intStart As Integer
Dim intNumCells As Integer

Set rng = ActiveSheet.Range("A2:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)

For Each c In rng
intStart = c.Value
intNumCells = c.Offset(0, 1).Value
c.Offset(0, intStart + c.Column - 1).Resize(1,
intNumCells).Interior.ColorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

--
Dianne

In om,
Curious typed:
Hi list,

Can someone please help me with a simple macro to do the following
calender-style function?

I have two columns

Start Length
3 5

I need a quick and dirty macro to shade the rows to the left. If
Start=3 then the first column to be shaded should be 3 columns away
from the start and (with length=5) the number of cells to be shaded
should be 5.

To explain better (I hope!!). Assume that we start with a1=Start,
a2=3, b1-length and b2=5 I would want cells e2-i2 shaded.

Does that make it any clearer?

Thanks in advance

Assume column A and Column B, Row 5

i = 5
Cells(i,3).Offset(0,Cells(i,1)).Resize(1,cells(i,2 )).Interior.ColorIndex = 6

--
Regards,
Tom Ogilvy

"Curious" wrote in message
om...
Hi list,

Can someone please help me with a simple macro to do the following
calender-style function?

I have two columns

Start Length
3 5

I need a quick and dirty macro to shade the rows to the left. If
Start=3 then the first column to be shaded should be 3 columns away
from the start and (with length=5) the number of cells to be shaded
should be 5.

To explain better (I hope!!). Assume that we start with a1=Start,
a2=3, b1-length and b2=5 I would want cells e2-i2 shaded.

Does that make it any clearer?

Thanks in advance

Sub macro1()
Dim c As Range
Dim c1 As Range
Dim rng As Range

For Each c In Sheets("Sheet1").Range("A:A")
If c.Row 1 And c 2 Then
Set rng = Sheets("Sheet1").Range(Cells(c.Row, c), _
Cells(c.Row, c.Row + c.Offset(0, 1) - 1))
For Each c1 In rng
With c1.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Next c1
End If
Next c
End Sub

HTH,
Merjet

My sincere thanks to all that posted here - it works a treat.

Don't suppose someone would like to explain _how_ it works now would they?
Please?

Thanks again

Since you received 3 answers and haven't specified which one you want
explained, I guess not.

--
Regards,
Tom Ogilvy

"Curious" wrote in message
om...
My sincere thanks to all that posted here - it works a treat.

Don't suppose someone would like to explain _how_ it works now would they?
Please?

Thanks again

Hi - me again.

I've tailored (read 'butchered') the macro now to read :

Sub Shade_cells()

Dim rng As Range
Dim c As Range
Dim intStart As Integer
Dim intNumCells As Integer


Set rng = Worksheets("ad_revenue").Range("E7:E" &
Worksheets("ad_revenue").Range("E65536").End(xlUp) .Row)


For Each c In rng
intStart = c.Value
intNumCells = c.Offset(0, 1).Value
c.Offset(0, intStart + c.Column).Resize(1,
intNumCells).Interior.ColorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

Can anyone tell me how to make this read from worksheets("ad_revenue")
and colour in worksheets("summary").

Again - many thanks in advance.

Sub Shade_cells()

Dim rng As Range
Dim c As Range
Dim intStart As Integer
Dim intNumCells As Integer
Dim strAddress As String

With Worksheets("ad_revenue")
Set rng = .Range("E7:E" & .Range("E65536").End(xlUp).Row)
End With

For Each c In rng
intStart = c.Value
intNumCells = c.Offset(0, 1).Value
strAddress = c.Offset(0, intStart).Resize(1, intNumCells).Address
Worksheets("summary").Range(strAddress).Interior.C olorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

In om,
Curious typed:
Hi - me again.

I've tailored (read 'butchered') the macro now to read :

Sub Shade_cells()

Dim rng As Range
Dim c As Range
Dim intStart As Integer
Dim intNumCells As Integer


Set rng = Worksheets("ad_revenue").Range("E7:E" &
Worksheets("ad_revenue").Range("E65536").End(xlUp) .Row)


For Each c In rng
intStart = c.Value
intNumCells = c.Offset(0, 1).Value
c.Offset(0, intStart + c.Column).Resize(1,
intNumCells).Interior.ColorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

Can anyone tell me how to make this read from worksheets("ad_revenue")
and colour in worksheets("summary").

Again - many thanks in advance.