LINNANDA
asked on
add ing borders using vba
Hi,
I've a macro which is re-formatting a sheet
I need to add a border from column a to column Q on a (i+4) row
however I'm getting an error on the
Range("A + (i+4)", "Q + (i+4)").Select line
Everything else is working fine:
-------------------------- ---------- ---------- ---------- ---------- ---------- --------
Sub Dis_b()
col = 3 'your column number
startRow = 1 ' Row where the data starts
col_header = 1
col7 = 7 'your column number
RowsA = Cells(Rows.Count, col).End(xlUp).row
For i = startRow To RowsA
If Cells(i, col) = "B" And Cells(i, col7) = "D" Then
For j = 1 To 5
Rows(i).EntireRow.Insert
Next j
Cells(i + 2, col_header).Value = "Model"
With Cells(i + 2, col_header)
' .Value = "Model 1 - Income"
.Font.Bold = True
End With
Cells(i + 3, col_header + 10).Value = "Alternative"
With Cells(i + 3, col_header + 10)
.Font.Bold = True
End With
Cells(i + 4, col7 + 3).Value = "Model"
With Cells(i + 3, col7 + 3)
.Font.Bold = True
End With
Cells(i + 4, col7 + 3).Value = "Actual"
With Cells(i + 3, col7 + 3)
.Font.Bold = True
End With
Cells(i + 4, col7 + 5).Value = "Variance"
With Cells(i + 3, col7 + 5)
.Font.Bold = True
End With
Cells(i + 3, col_header + 14).Value = "Other"
With Cells(i + 3, col_header + 14)
.Font.Bold = True
End With
Cells(i + 4, col7 + 7).Value = "Model"
With Cells(i + 4, col7 + 7)
.Font.Bold = True
End With
Cells(i + 4, col7 + 8).Value = "Actual"
With Cells(i + 4, col7 + 8)
.Font.Bold = True
End With
Cells(i + 4, col7 + 9).Value = "Variance"
With Cells(i + 4, col7 + 9)
.Font.Bold = True
End With
Cells(i + 3, col_header + 18).Value = "Fixed"
With Cells(i + 4, col_header + 18)
.Font.Bold = True
End With
Cells(i + 4, col7 + 11).Value = "Model"
With Cells(i + 4, col7 + 11)
.Font.Bold = True
End With
Cells(i + 4, col7 + 12).Value = "Actual"
With Cells(i + 4, col7 + 12)
.Font.Bold = True
End With
Cells(i + 4, col7 + 13).Value = "Variance"
With Cells(i + 4, col7 + 13)
.Font.Bold = True
End With
Cells(i + 3, col_header + 22).Value = "Cash"
With Cells(i + 3, col_header + 22)
.Font.Bold = True
End With
Cells(i + 4, col7 + 15).Value = "Model"
With Cells(i + 4, col7 + 15)
.Font.Bold = True
End With
Cells(i + 4, col7 + 16).Value = "Actual"
With Cells(i + 4, col7 + 16)
.Font.Bold = True
End With
Cells(i + 4, col7 + 17).Value = "Variance"
With Cells(i + 4, col7 + 17)
.Font.Bold = True
End With
'Cells (i + 4)
Range("A + (i+4)", "Q + (i+4)").Select
With Selection.Borders(xlEdgeBo ttom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End If
Next i
End Sub
I've a macro which is re-formatting a sheet
I need to add a border from column a to column Q on a (i+4) row
however I'm getting an error on the
Range("A + (i+4)", "Q + (i+4)").Select line
Everything else is working fine:
--------------------------
Sub Dis_b()
col = 3 'your column number
startRow = 1 ' Row where the data starts
col_header = 1
col7 = 7 'your column number
RowsA = Cells(Rows.Count, col).End(xlUp).row
For i = startRow To RowsA
If Cells(i, col) = "B" And Cells(i, col7) = "D" Then
For j = 1 To 5
Rows(i).EntireRow.Insert
Next j
Cells(i + 2, col_header).Value = "Model"
With Cells(i + 2, col_header)
' .Value = "Model 1 - Income"
.Font.Bold = True
End With
Cells(i + 3, col_header + 10).Value = "Alternative"
With Cells(i + 3, col_header + 10)
.Font.Bold = True
End With
Cells(i + 4, col7 + 3).Value = "Model"
With Cells(i + 3, col7 + 3)
.Font.Bold = True
End With
Cells(i + 4, col7 + 3).Value = "Actual"
With Cells(i + 3, col7 + 3)
.Font.Bold = True
End With
Cells(i + 4, col7 + 5).Value = "Variance"
With Cells(i + 3, col7 + 5)
.Font.Bold = True
End With
Cells(i + 3, col_header + 14).Value = "Other"
With Cells(i + 3, col_header + 14)
.Font.Bold = True
End With
Cells(i + 4, col7 + 7).Value = "Model"
With Cells(i + 4, col7 + 7)
.Font.Bold = True
End With
Cells(i + 4, col7 + 8).Value = "Actual"
With Cells(i + 4, col7 + 8)
.Font.Bold = True
End With
Cells(i + 4, col7 + 9).Value = "Variance"
With Cells(i + 4, col7 + 9)
.Font.Bold = True
End With
Cells(i + 3, col_header + 18).Value = "Fixed"
With Cells(i + 4, col_header + 18)
.Font.Bold = True
End With
Cells(i + 4, col7 + 11).Value = "Model"
With Cells(i + 4, col7 + 11)
.Font.Bold = True
End With
Cells(i + 4, col7 + 12).Value = "Actual"
With Cells(i + 4, col7 + 12)
.Font.Bold = True
End With
Cells(i + 4, col7 + 13).Value = "Variance"
With Cells(i + 4, col7 + 13)
.Font.Bold = True
End With
Cells(i + 3, col_header + 22).Value = "Cash"
With Cells(i + 3, col_header + 22)
.Font.Bold = True
End With
Cells(i + 4, col7 + 15).Value = "Model"
With Cells(i + 4, col7 + 15)
.Font.Bold = True
End With
Cells(i + 4, col7 + 16).Value = "Actual"
With Cells(i + 4, col7 + 16)
.Font.Bold = True
End With
Cells(i + 4, col7 + 17).Value = "Variance"
With Cells(i + 4, col7 + 17)
.Font.Bold = True
End With
'Cells (i + 4)
Range("A + (i+4)", "Q + (i+4)").Select
With Selection.Borders(xlEdgeBo
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
Application.ScreenUpdating
Application.Calculation = xlCalculationAutomatic
Exit Sub
End If
Next i
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER