+ Reply to Thread
Results 1 to 6 of 6

Simplified Conditional Macro

Hybrid View

  1. 03-27-2011, 12:20 PM #1
    martinez_pedro
    martinez_pedro is offline
    Forum Contributor
    Join Date
    09-29-2008
    Location
    usa
    MS-Off Ver
    EXCEL 2010
    Posts
    116

    Simplified Conditional Macro

    Is there a way to simplified this conditional macro?

    Before hand thank you for all help provided

    Columns("I:I").Select
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""WICHITA"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""CHENEY"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""COLWICH"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GARDEN PLAIN"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GODDARD"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""KECHI"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ROSE HILL"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""SEDGWICK"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""VALLEY CENTER"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 3407718
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
    Last edited by martinez_pedro; 03-27-2011 at 05:51 PM.
    Reply With QuoteRegister To Reply
  2. 03-27-2011, 01:07 PM #2
    HSV
    HSV is offline
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    863

    Re: Simplified Conditional Macro

    This does the same?
    With Selection

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""KECHI"""
.FormatConditions(1).Interior.Color = 3407718
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ROSE HILL"""
.FormatConditions(2).Interior.Color = 3407718
.FormatConditions(2).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""SEDGWICK"""
.FormatConditions(3).Interior.Color = 3407718
.FormatConditions(3).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""VALLEY CENTER"""
.FormatConditions(4).Interior.Color = 3407718
.FormatConditions(4).StopIfTrue = False

End With
    Harry.
    Reply With QuoteRegister To Reply
  3. 03-27-2011, 01:09 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simplified Conditional Macro

    Hi,

    Perhaps something like...

        With Range("I:I")
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=OR(i1=""CHENEY"",i1=""COLWICH"",i1=""GARDEN PLAIN"",i1=""GODDARD"",i1=""KECHI"",i1=""ROSE HILL"",i1=""SEDGWICK"",i1=""VALLEY CENTER"",i1=""WICHITA""" & ")"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Range("i:i").FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 3407718
            .TintAndShade = 0
        End With
    End With
    I also question whether you need to format the whole of column I. Won't just the relevant range you're using suffice?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 03-27-2011, 05:50 PM #4
    martinez_pedro
    martinez_pedro is offline
    Forum Contributor
    Join Date
    09-29-2008
    Location
    usa
    MS-Off Ver
    EXCEL 2010
    Posts
    116

    Re: Simplified Conditional Macro

    So try both suggestion and i combine them and this is what i came up with
    it when from 577 lines of code to 150
    Thanks guys for you help 
    Sub TETS2()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("I2:I" & LR).Select
With Selection
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""AUGUSTA"""
.FormatConditions(1).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""BELLA VISTA"""
.FormatConditions(2).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""BENTONVILLE"""
.FormatConditions(3).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""CENTERTON"""
.FormatConditions(4).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""CAVE SPRINGS"""
.FormatConditions(5).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GRAVETTE"""
.FormatConditions(6).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""HIWASSE"""
.FormatConditions(7).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""FAYETTEVILLE"""
.FormatConditions(8).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ELKINS"""
.FormatConditions(9).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""FARMINGTON"""
.FormatConditions(10).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GOSHEN"""
.FormatConditions(11).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GREENLAND"""
.FormatConditions(12).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""LINCOLN"""
.FormatConditions(13).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""PRAIRIE GROVE"""
.FormatConditions(14).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""WEST FORK"""
.FormatConditions(15).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""WINSLOW"""
.FormatConditions(16).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ALMA"""
.FormatConditions(17).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""FORT SMITH"""
.FormatConditions(18).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GREENWOOD"""
.FormatConditions(19).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""VAN BUREN"""
.FormatConditions(20).Interior.Color = 3407718
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""ARKOMA"""
.FormatConditions(21).Interior.Color = 3407718
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
End With
End Sub
    Reply With QuoteRegister To Reply
  5. 03-27-2011, 06:49 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Simplified Conditional Macro

    Why not put all those names in a range and have just one format condition with a simple MATCH formula?
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 03-27-2011, 10:31 PM #6
    martinez_pedro
    martinez_pedro is offline
    Forum Contributor
    Join Date
    09-29-2008
    Location
    usa
    MS-Off Ver
    EXCEL 2010
    Posts
    116

    Re: Simplified Conditional Macro

    Hey shg can you give me an idea how to piece together this formula?
    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1