Formatting entire row based on text in first column

[agnesz]

I have a spreadsheet which aligns names to different areas. THis gets updated monthly and each time I need to reformat the whole document to highlight and border new things. Is there a way to write a macro where depending on the text in column B, that whole row ranging from col B to V would get reformatted?

To illustrate:

If Col B = "Planner" or "MPD", then all of that row should be size 8, cells in B and C should get highlighted in gray and range b through v should get a thick outline border.

If Col B = "DDP" or "GMM", then all of that row should be size 10, cells in B and C should get highlighted in gray and range b through v through get a thick outline border.

Thanks:motz2:

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, TEST_COLUMN).Value = "Planner" Or .Cells(i, TEST_COLUMN).Value = "MPD" Then

.Rows(i).Font.Size = 8
With .Cells(i, "B")
.Resize(, 2).Interior.ColorIndex = 16
With .Resize(, 21)
.BorderAround ColorIndex:=xlcolorinfexautomatic, Weight:=xlThick
End With
End With
ElseIf .Cells(i, TEST_COLUMN).Value = "DDP" Or .Cells(i, TEST_COLUMN).Value = "GMM" Then

.Rows(i).Font.Size = 10
With .Cells(i, "B")
.Resize(, 2).Interior.ColorIndex = 16
With .Resize(, 21)
.BorderAround ColorIndex:=xlcolorinfexautomatic, Weight:=xlThick
End With
End With
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub