Macro help

Posted by Rob on August 02, 2001 1:50 PM

Hi everyone,

Ivan Moala gave me this code, I was wondering if he, or someone else could modify it to format ALL cells with data in them, not just the first row. Also, the code puts a formula in column C which adds the first 2 columns. What if I wanted it to put the formula in column D which added columns A, B, and C? I've tried playing with it, but like I told him, without much success. I appreciate the help :) Heres the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Columns.Count &LT; 2 Then
If Target &LT;&GT; "" Then
With Target
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
'bold
.Font.Bold = True
'add formula to "C"
.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
End With
End If
End If
End If

End Sub

Thanks,
Rob

Posted by Cory on August 02, 2001 2:25 PM

Here you go! This worked fine for me. You should be able to figure out how to modify it on your own now that you have two code block to compare... Have fun!

ActiveCell.Offset(-1, 1).Borders(xlEdgeLeft).LineStyle = xlContinuous
ActiveCell.Offset(-1, 1).Borders(xlEdgeTop).LineStyle = xlContinuous
ActiveCell.Offset(-1, 1).Borders(xlEdgeBottom).LineStyle = xlContinuous
ActiveCell.Offset(-1, 1).Borders(xlEdgeRight).LineStyle = xlContinuous

ActiveCell.Offset(-1, 2).Borders(xlEdgeLeft).LineStyle = xlContinuous
ActiveCell.Offset(-1, 2).Borders(xlEdgeTop).LineStyle = xlContinuous
ActiveCell.Offset(-1, 2).Borders(xlEdgeBottom).LineStyle = xlContinuous
ActiveCell.Offset(-1, 2).Borders(xlEdgeRight).LineStyle = xlContinuous
'bold
.Font.Bold = True
ActiveCell.Offset(-1, 1).Font.Bold = True
ActiveCell.Offset(-1, 2).Font.Bold = True
ActiveCell.Offset(-1, 3).Font.Bold = True
'add formula to "C"
.Offset(0, 3).FormulaR1C1 = "=RC[-2]+RC[-1]+RC[-3]"
End With
End If
End If
End If

End Sub

Cory