Simple Excel Macro - Please Help

  • Thread starter Thread starter Curious
  • Start date Start date
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
 
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
 
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
 
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.
 
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.ColorIndex = 4
Next c

Set c = Nothing
Set rng = Nothing

End Sub

In
 
Back
Top