VBA via Recorder

[zprilliman]

Good evening,
I am trying to clean up a macro that I recorded with the recorder. Its basic function of the macro is to filter all the data labeled to Phoenix (Column B), paste it on a new tab and then sort and subtotal by column G. I've gotten the majority of that accomplished, but I tried adding some extra tasks in the macro and I'm not sure how to clean it up. I feel like I'm 99% there, I need to reconfigure a portion of the macro to basically state that if the cells in Column K are RGB (255, 255, 255) to conditionally format them. I only need the white cells with values to be part of the formatting. I used the recorder to build the conditional formatting but I can't figure out how to write the If statement to make it valid.

'Format cost column
Range(Range("K2"), Range("K2").End(xlDown)).Select
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="=500"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
EndWith
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=-500"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        Selection.FormatConditions(1).StopIfTrue = False
EndWith

 

[RickXL]

Hi,

I have added a section of code that looks at the cells in K and keeps all the white ones in a range.
Then that range is used to create your existing conditional format.

Sub ConditionalFormat()
    Dim rCell As Range, rFormat As Range
    
    For Each rCell In ActiveSheet.Range(Range("K2"), Range("K2").End(xlDown))
        If rCell.Interior.Color = RGB(255, 255, 255) Then
            If rFormat Is Nothing Then
                Set rFormat = rCell
            Else
                Set rFormat = Application.Union(rFormat, rCell)
            End If
        End If
    Next rCell
    
    'Format cost column
    With rFormat
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=500"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=-500"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
       
    End With
End Sub

 

[zprilliman]

Hey man this is perfect, I rearranged a part of my commands and inserted your corrects and it works great.

Thank you.

 

[RickXL]

Glad you liked it and thanks for the feedback.

Regards,