Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Condition Formating > 3 Condintions: Can It be done?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Kind of a begginner with Macros. Hope someone can help. I am using Conditional Formating to highlight a group of cells with a given value. These values autoupdate from data data on other worksheets. I have 10 values I am trying to color code but conditional formating only allows 3 conditions. I tried to manually do this with VB script (Stare and Compare...a begginers best friend) but the macro errors out after the 3rd condition. Any ideas:

    Sub Color()
    '
    ' Color Macro
    ' Macro recorded 3/27/02 by
    '

    '
    Range("A1:P34").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=$B$39"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=$B$40"
    Selection.FormatConditions(2).Interior.ColorIndex = 38
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=$B$41"
    Selection.FormatConditions(3).Interior.ColorIndex = 8
    End Sub

  2. #2
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes.

    Try here for starters...

    Also, using the search feature before posting can sometimes save some time...

    _________________
    Kristy

    "There are two means of refuge from the miseries of life: music and cats." - Albert Schweitzer

    [ This Message was edited by: Von Pookie on 2002-03-27 13:57 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Did a search, guesse it wasn't as good as yours. Tried to apply the solutions given but to no avail. I did find this with another source. It applied directly to what I was trying to do. Hope this helps someone with the same problem.Sub Add_Color()

    'AII Red 3
    'IITX Light Purple 38
    'COCOMP Pale Blue 8
    'JTR Light Green 35
    'MCOMP Yellow 36
    'TRCOMP Yellow 36
    'TMCOMP Purple 39
    'TECOMP Purple 39
    'NO WORK Purple 39
    'IGNORE Purple 39

    Dim TmpString As String
    Dim TmpColor As Integer

    For X = 1 To 30

    Range(Cells(3, X), Cells(3, X)).Select
    If ActiveCell.Value = "" Then GoTo X_DONE

    For Y = 3 To 10000
    Range(Cells(Y, X), Cells(Y, X)).Select
    If ActiveCell.Value = "" Then GoTo Y_DONE

    TmpString = ActiveCell.Value
    TmpString = Mid(TmpString, 1, 3)

    TmpColor = 0
    If TmpString = "AII" Then TmpColor = 3
    If TmpString = "IIT" Then TmpColor = 38
    If TmpString = "COC" Then TmpColor = 8
    If TmpString = "JTR" Then TmpColor = 35
    If TmpString = "MCO" Then TmpColor = 36
    If TmpString = "TRC" Then TmpColor = 36
    If TmpString = "TMC" Then TmpColor = 39
    If TmpString = "TEC" Then TmpColor = 39
    If TmpString = "NO " Then TmpColor = 39
    If TmpString = "IGN" Then TmpColor = 39

    If TmpColor > 0 Then
    With Selection.Interior
    .ColorIndex = TmpColor
    .Pattern = xlSolid
    End With
    End If

    Next Y
    Y_DONE:

    Next X

    X_DONE:

    End Sub


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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