VBA via Recorder

zprilliman

New Member
Joined
Apr 22, 2014
Messages
6
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.

Rich (BB code):
'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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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.

Code:
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

New Member
Joined
Apr 22, 2014
Messages
6
Hey man this is perfect, I rearranged a part of my commands and inserted your corrects and it works great.

Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,989
Messages
5,465,849
Members
406,450
Latest member
gunner12

This Week's Hot Topics

Top