Looping a format style macro

[Aussiebear]

I am seeking a solution to enable a format style to be applied to a worksheet. Whilst I have experiemented with the macro recorder and shortened up the code it built, I need the code to loop down the sheet as long as there is data in Column A

[Bob Phillips]

[vba]

Sub FormatCells()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRow Mod 2 = 0 Then LastRow = LastRow + 1

For i = 2 To LastRow Step 2

With .Cells(i, "A").Resize(, 3).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With

With .Cells(i, "D").Resize(2, 4)

.BorderAround LineStyle:=xlContinuous

.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next i
End With
End Sub
[/vba]

[Aussiebear]

Thank you Bob. Once the style is applied to the sheet, can I simply change the sub title from Sub FormatCells() to Sub Worksheet_Change(ByVal Target As Range) so that it comes into effect as soon as I insert a new pair of rows?

[Bob Phillips]

I don't think it would be a straight lift Ted.

You would need to determine what triggers the styling, and what to do if the value is removed, etc.

[RonMcK]

Aussiebear,

Did you try conditional formating? See the attached copy of your workbook; I added conditional formatting to it. I think it will work for you, and it resolves the issue of what to do when new line pairs are added, inserted or a line pair deleted or cleared.

Thanks,