VBA Conditional Formatting Help

[VinchinzoB]

Hello,

So quick and easy. I'm trying to get the following to highlight and bold based on certain criteria:

If P5 = Blocked, then the range of B5:Q5 needs to be bold and highlight the color red.
If P5 = Return to Vendor, Obsolete, or Need Copy, then the range of B5:Q5 needs to be bold and highlight the color yellow.
If P5 = blank or anything else, then no formatting needs to be down.

I would like to note that the number of rows this formatting needs to potentially affect will vary, but it will always start with row 5 and go down.

[MINCUS1308]

This is a pretty standard thing to do in excel.
You should browse this article.

https://www.gcflearnfree.org/excel2016/conditional-formatting/1/ (https://www.gcflearnfree.org/excel2016/conditional-formatting/1/)

[VinchinzoB]

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

[MINCUS1308]

This can be accomplished with 4 rules.
These rules apply to rows 5-6000.
and the rules are case sensitive.

Here are the four rules:
21875

Here is a look at one of the formulas:
21876

Here Is the output:
21874


I have also attached the file here so you can look at each of the rules closer.
21877

[MINCUS1308]

ah, I see. That's what I thought at first and I wrote the code to do it. After re-reading I thought I had misunderstood. Disregard my previous post and I will rewrite my code real fast

Edit: It sounds and looks like you have a solution - Sorry for the misunderstanding