Not trying to sound rude, but I'm not looking for a basic conditional formatting. I understand how to use that. However, I have pivot tables that are automatically updating in reference to a table that I have setup via macro. What's happening is that when my initial macro is ran, the table automatically inserts rows and it fragments my conditional formatting; however, I was able to get my macro to work with the following:
Sub Format ()
Dim LastRow As Long
Dim Cell As Range
Dim i As Integer
With Sheets("Recon")
LastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
For i = 5 To LastRow
Range("P" & i).Select
For Each Cell In Selection
If Cell.Value = "Blocked" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = 22
ElseIf Cell.Value = "Return to Vendor" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = 19
ElseIf Cell.Value = "Need Copy" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = 19
ElseIf Cell.Value = "Obsolete" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = 19
Else
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = xlNone
End If
Next
For Each Cell In Selection
If Cell.Value = "Blocked" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Font.Bold = True
ElseIf Cell.Value = "Return to Vendor" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Font.Bold = True
ElseIf Cell.Value = "Need Copy" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Font.Bold = True
ElseIf Cell.Value = "Obsolete" Then
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Font.Bold = True
Else
Range(ActiveCell.Offset(0, -14), ActiveCell.Offset(0, 1)).Interior.ColorIndex = xlNone
End If
Next
Next i
End With
End Sub