VBA & Conditional Formatting

[th259]

I have a table that was pasted values from a pivot table. The first and second column (Fund and Vendor Name columns) have subtotals. I have the following code to highlight any subtotal rows for column A, but how do I do it for column B?

Dim myRange As Range
    Set myRange = ThisWorkbook.Worksheets("Summary").Range("A6:J" & Range("J" & Rows.Count).End(xlUp).Row)
    With myRange.Borders
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With myRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(NOT(ISBLANK($A6)),ISBLANK($E6))"
           .FormatConditions(.FormatConditions.Count).Interior.Color = 15189684
        End With
    myRange.Select
    Selection.AutoFilter
    ActiveSheet.myRange.AutoFilter Field:=2, Criteria1:=RGB(180, 198, 231), Operator:=xlFilterCellColor
End Sub

I had tried the code below and it didn't work.

With myRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SEARCH (" * Total * ", $B6)"
           .FormatConditions(.FormatConditions.Count).Interior.Color = 11854022
        End With

Also, how do I filter the Fund column (column A) to only show the rows with the specific blue fill color and delete the word Total from only those filtered rows then add a vlookup formula in column B only for those filtered rows.

 

[MARK858]

If that includes the Grand Total row you just need a - 1

Range("B7:B" & Range("J" & Rows.Count).End(xlUp).Row - 1)

 

[th259]

Thank you very much, it worked!

 

[MARK858]

You're welcome