Try adding the code in red:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim rInt As Range
Dim rng As Range
Set rInt = Intersect(Target, Range("A9:BM50000"))
If Not rInt Is Nothing Then
For Each cell In rInt
Select Case cell.Value
Case "X"
cell.Interior.ColorIndex = 3
<snip>
cell.Font.Bold = True
cell.HorizontalAlignment = xlCenter
Case 0
cell.Interior.ColorIndex = 0
cell.HorizontalAlignment = xlCenter
End Select
If cell.Column = 1 And Len(cell.Value) > 0 Then
With cell.Resize(, 65)
.BorderAround xlContinuous, xlThin, xlAutomatic
.HorizontalAlignment = xlCenter
End With
End If
Next cell
End If
If Not Intersect(Target, Range("D9:D50000")) Is Nothing Then
Application.EnableEvents = False
Colorize Intersect(Target, Range("D9:D50000"))
Application.EnableEvents = True
End If
End Sub
Note that I have paid attention to your "if it is blank do nothing", which means that if a cell in column A which once had something in it and had caused the row to be formatted, becomes blank, then that row's formatting will remain unchanged.
Also, this doesn't strictly go through the whole A9:A50000, only Intersect(Target, Range("A9:BM50000")) but it will format the whole row (A:BM) if that range includes non-empty cells in column A; post back if this is wrong.
Bookmarks