Simple Excel Macro - Please Help

C

Curious

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
 
D

Dianne

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
 
T

Tom Ogilvy

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
 
M

merjet

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
 
C

Curious

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
 
T

Tom Ogilvy

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

Curious

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

Dianne

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.ColorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

In
 
Top