Perksy_no1
Well-known Member
- Joined
- Oct 27, 2011
- Messages
- 598
- Office Version
- 365
- Platform
- Windows
Hi all,
I've got a macro which loops down column G looking for the word "projected buy". I then offset using a variable (r1) I then need to off set it some more based on the value in the corresponding row in column B, format the cell then carrying on down column G looking for the next "projected buy"
I can most of this code working but I cant get it to offset buy the value in column B. I've highlighted the part of the code im struggling with
any help would be much appreciated, Thanks in advance Mark
I've got a macro which loops down column G looking for the word "projected buy". I then offset using a variable (r1) I then need to off set it some more based on the value in the corresponding row in column B, format the cell then carrying on down column G looking for the next "projected buy"
I can most of this code working but I cant get it to offset buy the value in column B. I've highlighted the part of the code im struggling with
any help would be much appreciated, Thanks in advance Mark
Rich (BB code):
Dim r1 As Long
Dim r2 As Long
Dim LastRow As Long
r1 = Sheets("Sheet1").Range("B1").Value
r2 = Sheets("Sheet1").Range("B2").Value
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("G6").Select
Do
If ActiveCell.Value = "Projected Buy" Then
Selection.Offset(0, r1 + "RC[-5]").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Offset(1, r2).Select
Else
Selection.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
