none
Running a macro in EXCEL - "Conditional Formatting" functions are not restricted to the indicated cell range. RRS feed

  • Question

  • I created a macro to do conditional formatting on data dropped only in specific cells.

    The macro looks for string text matches to highlight them for one cell range, as well as doing number comparisons in another.

    However, when I use the Range(A1:A1000).Select command, the conditional formatting affects cells outside the range that I didn't want touched, even if they may contain partial strings or numbers that matched the conditions within the range.

    How do I make this range selection exclusive and not touch the rest of the spreadsheet?

    ____________________________________________________

    Sub ConditionalFormLeads()
    '
    ' ConditionalFormLeads Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+L
    '
        Range("J16:J1000").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=31"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16752384
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13561798
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=30"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16751204
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10284031
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=120"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlTextString, String:="Not", _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Borders(xlLeft)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlRight)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlTop)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlBottom)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlTextString, String:="N/A", _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Borders(xlLeft)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlRight)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlTop)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Borders(xlBottom)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

        Range("I16:I1000").Select

        Selection.FormatConditions.Add Type:=xlTextString, String:="Lead Lost", _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlTextString, String:="Lead Sold", _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16752384
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13561798
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

    Friday, January 13, 2017 5:12 PM

All replies

  • How do I make this range selection exclusive and not touch the rest of the spreadsheet?

    Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Replace SELECT with a WITH instruction:

      Range("A1:A1000").Select
    becomes
      With Range("A1:A1000")

    Then remove all "Selection" words and finally add
      End With
    at the bottom.

    So your code looks like the sample below.

    Andreas.

    Sub ConditionalFormLeads()
      With Range("A1:A1000")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=31"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
          .Color = -16752384
          .TintAndShade = 0
        End With
          
        'etc.
        
      End With
    End Sub

    Saturday, January 14, 2017 8:19 AM