Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: More than 3 conditional formats

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default More than 3 conditional formats

    I Need to be able to format a large range of cells according to the number that they contain. Basically the cells are numbered 1-12 based on a variety of equations from other data and are updated frequently. I need the cells to be 12 different colors based on the value. Conditional formatting works great but only for 3 conditions. What is the easiest way to expand my conditions? I have read several tips for similar scenarios, but I cannot get any of the code to execute. Please give me some suggestions!!!

  2. #2
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    Walsall, England
    Posts
    2,831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    What code are you having problems executing - care to post please with your conditions?

    Edit: Just as an aside - something that came up this evening: http://www.mrexcel.com/board2/viewto...229&highlight=
    Iridium


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    Easiest way I can think of is to have the code in a Worksheet_Change event on the sheet you're using.

    Right click on the sheet tab and hit "view code". Toss your color criteria in a macro similar to:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim VRange As Range
        Set VRange = Range("RangeYouWantFormated")
        
        For Each cell In Target
            If Union(cell, VRange).Address = VRange.Address Then
             
            '(toss in your format code here)
    
             
            End If
        Next cell
    End Sub
    HTH
    Adam

    [edit] - erm...what Iridium said (lol)

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,697
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  5. #5
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    Walsall, England
    Posts
    2,831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by Adam
    [edit] - erm...what Iridium said (lol)
    Only cos I edited my message first!
    Iridium


  6. #6
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    This is a code that looks like it could be adapted to meet my needs, but I am not sure how to get it to work in the first place. I would greatly appreciate some tutoring. Thanks for your feedback thus far.

    Here's a second version (see previous listing) that sets cell back to defaults if the grade is removed.


    code:
    --------------------------------------------------------------------------------

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    With Target
    If .Column = 1 Then
    Select Case Val(.Value)
    Case 90 To 100 ' A
    .Interior.ColorIndex = 4
    Case 80 To 89 ' B
    .Interior.ColorIndex = 4
    Case 70 To 79 ' c
    .Interior.ColorIndex = 4
    Case 60 To 69 ' D
    .Interior.ColorIndex = 4
    Case 1 To 59 ' F
    .Interior.ColorIndex = 4
    Case Else 'reset to default if non of above
    .Interior.ColorIndex = xlNone
    .Font.ColorIndex = 0
    End Select
    End If
    End With
    End Sub

    --------------------------------------------------------------------------------




    [ This Message was edited by: Nimrod on 2002-05-21 01:15 ]

  7. #7
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    Also, I tried the code you posted on another topic as listed below. I am just missing a basic concept of VBA. So I have numbers between 1-30 in cells a:1-a:10. I paste the code in as instructed, but none of the formatting changes and the only thing I get is that when I try to change one of the numbers I now get an error message. WHAT AM I NOT GETTING HERE???

    code:
    --------------------------------------------------------------------------------


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Getting past Conditional Formattings 3 Criteria Limit
    'This code must be placed in the Private Module of the Worksheet. _
    To get there right click on the sheet name tab and select "View Code".
    'Excel's very handy Conditional Formatting unfortunately only allows up to 3 conditions. _
    The method below gets around this limit. It is set to work on A1:A10 only. event.

    Dim icolor As Integer
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Select Case Target
    Case 1 To 5
    icolor = 6
    Case 6 To 10
    icolor = 12
    Case 11 To 15
    icolor = 7
    Case 16 To 20
    icolor = 53
    Case 21 To 25
    icolor = 15
    Case 26 To 30
    icolor = 42
    Case Else
    'Whatever you want
    End Select
    Target.Interior.ColorIndex = icolor
    End If

    End Sub

  8. #8
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    Actually, I think I may have figured it out, but I would still love any other pointers any of you may have on this topic and a bit of an explanation of what exactly the code is saying at the beginning:

    (Dim icolor As Integer
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Select Case Target)

    What is "if not intersect is nothing then" really saying?

  9. #9
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More than 3 conditional formats

    Actually, I am still missing a piece. I got it to work if I go type in a number in one of the cells, but the value of the cell is based on an equation. For example: =IF(ISERROR(N44)=TRUE,O43,N44). In this particular case the cell in question is equal to 1, but the next time I update the spreadsheet the cell may be equal to 3. How do I get the formatting to change accordingly? I really appreciate the help!

  10. #10
    New Member
    Join Date
    Feb 2004
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Still hoping for some more help

    Please give me some more help on this! THANKS!

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
  •