Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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

Simple Excel Macro - Please Help
 

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.

Simple Excel Macro - Please Help
 

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.