+ Reply to Thread
Results 1 to 2 of 2

multiple conditional formatting

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-27-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    290

    multiple conditional formatting

    my current code is:

        Sheets("Time_cycle_of_S5_requests_UK").Select
        Columns("H:J").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Columns("H:J").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=7"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With

    however this doesnt seem to do what i want it to do...
    i wish for all values less then 0 to be in red and all values more then 7 to be in orange.
    as they dont conflict i shouldnt have to set priority.

    however what actually happens is i get the "less then 0" condition to be orange and the more than 7 condition is just normal formatting... how can i fix this?

    EDIT: i was missing the line
        Selection.FormatConditions(1).StopIfTrue = False
    however this now makes my header orange... is there any way to prevent that without setting a range like "H2:J30000"
    as my data size could increase i would like to be able to add to the data without having to re-highlight or increase the range of the formatting.
    Last edited by penfold1992; 10-09-2012 at 05:40 AM.

  2. #2
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: multiple conditional formatting

    Best way to do it using your existing code is to clear the formatting conditions from the headers:
    Sub Formatter()
        Sheets("Time_cycle_of_S5_requests_UK").Select
        Columns("H:J").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=7"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Range("H1:J1").Select
        Selection.FormatConditions.Delete
        Columns("H:J").Select
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1