Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulate Conditional formatting (2K)

    Hi,
    Is it possible to do more than three conditional formats?
    I have a spreadsheet where the user changes the colour of the cell dependant upon the month. On a second sheet I have a list of months with the corresponding colour. Is it possible to automate this, so say March = Cyan, user enters 11/3/2006 in cell, cell background turns to Cyan?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formulate Conditional formatting (2K)

    For 1 cell you can have 4 formats: 1 explicit format and 3 conditional formats.

    The only workaround is via code.see some of the answers to <post:=355,924>post 355,924</post:> for some ideas. Post back with any questions on the techniques

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    You could use the Worksheet_Change event. Here is an example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A:A")).Cells
    If IsDate(oCell) Then
    Select Case Month(oCell)
    Case 1
    oCell.Interior.ColorIndex = 3
    Case 2
    oCell.Interior.ColorIndex = 5
    Case 3
    oCell.Interior.ColorIndex = 9
    Case 4
    oCell.Interior.ColorIndex = 4
    Case 5
    oCell.Interior.ColorIndex = 7
    Case 6
    oCell.Interior.ColorIndex = 8
    Case 7
    oCell.Interior.ColorIndex = 11
    Case 8
    oCell.Interior.ColorIndex = 15
    Case 9
    oCell.Interior.ColorIndex = 6
    Case 10
    oCell.Interior.ColorIndex = 10
    Case 11
    oCell.Interior.ColorIndex = 12
    Case 12
    oCell.Interior.ColorIndex = 13
    End Select
    Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End If
    Next oCell
    End If
    End Sub

    (The choice of colours is atrocious - I just typed some random numbers; you can use 1 ... 56)

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulate Conditional formatting (2K)

    Thanks Hans, this is just what I was after.
    Just out of interest. I also have conditional formatting for this cells, where if the date in the cell is less than today the cell colour is red. If the cell value is "WARRANT", then the colour is orange. Is this also something that I could include in this code?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    You can expand the code - put in more If ... statements, or ElseIf ... statements to test the cell's value.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulate Conditional formatting (2K)

    Sorry,
    So something like:

    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("H:H")).Cells

    If oCell = "WARRANT" Then
    oCell.Interior.ColorIndex = 55

    If oCell < Now Then
    oCell.Interior.ColorIndex = 3



    If IsDate(oCell) Then

    Select Case Month(oCell)
    Case 1
    oCell.Interior.ColorIndex = 4
    Case 2
    oCell.Inter

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    For each If ... Then (on a separate line) there must be an End If. (But you can combine If blocks using ElseIf)
    Consistent indentation helps to see understand the structure of the code.
    It doesn't make sense to test if the cell is before Now first, and only then test if it is a date.

    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("H:H")).Cells
    If oCell = "WARRANT" Then
    oCell.Interior.ColorIndex = 55
    ElseIf IsDate(oCell) Then
    If oCell < Date Then
    oCell.Interior.ColorIndex = 3
    Else
    Select Case Month(oCell)
    Case 1
    oCell.Interior.ColorIndex = 4
    Case 2
    ...
    End Select
    End If
    Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End If
    Next oCell
    End If

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulate Conditional formatting (2K)

    Thanks Hans, this does make sense. I'm still somewhat unfamiliar with excel VBA

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formulate Conditional formatting (2K)

    Good morning, Hans...I have been looking at the code you suggested (and the posts by yourself and armitrageshanks)....can you give me a brief explanation as to how it "works"...I gather than (a) the data entered into column A will trigger color changes that accord with the ColorIndex=?? and it appears that the data will be some form of date (the month, it looks like).....or ( data entered elsewhere that causes a date to appear in column A will be the 'trigger' to change the color ??..??.......when I put a series of dates into column A, nothing happens and so I am clearly missing something relative to how the data gets into column A (ie: what is the 'trigger' for the color change and how does the ''trigger' relate to the contents of column A).....ps: I need a good, starter book on VBA for Excel 2003, one that will explain the concepts with examples so that I can relate the two...thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    ArmitageShanks wanted to enter dates into column A, and have the cells colored according to the month of the year.

    The Worksheet_Change event occurs whenever the user enters, edits or deletes a value in one or more cells. It does not occur when the value of a cell changes as the result of a formula or through VBA code.
    The Worksheet_Change procedure *must* be in the worksheet module that you open by right-clicking the sheet tab and selecting View Code from the popup menu. It won't do anything in a standard module.
    The Worksheet_Change event procedure provides an argument Target. This is filled automatically by Excel, and it is a range containing all cells that have been changed (usually just one cell, but it could be a block of cells). If you only want to act if specific cells have been changed, you check whether the Target intersects with the specific range that you want to handle. In the example in <post:=557,373>post 557,373</post:>, we only look at column A. Changes in all other columns are ignored.
    So if the user enters a value in, say, A5, the code will do something. If the user enters a value in B3, the code will exit immediately, without doing anything.

    See Excel Books by John Walkenbach for a series of excellent <img src=/S/pun.gif border=0 alt=pun width=22 height=18> books. He has a beginner book about Excel 2003 VBA and a more advanced one.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formulate Conditional formatting (2K)

    I think I am starting to understand a bit..so, for example, say I have a worksheet A5:F20; I have already used my 3 cond formatting options; a 4th cond format option will not work if it is based on a formula in another cell, but it will work if it is based on data entered directly into a cell...so, if I want a 4th option (eg: the cell goes green if I enter the words "AWAY" or "NOT AT WORK"), then I use the code you suggested and the range is A5:F20 (so that it applies to the whole range) and the IFstatement is IF (or("AWAY","NOT AT WORK")(oCell) Case 1 oCell.Interior.colorindex = 4 (green)......is "oCell" the letter o followed by Cell ??

    FOLLOW-UP: I modifed the code you suggested to armitageshanks to (try to) make the cell go green is I enter Away in a cell in column A....but I keep getting an error message....see attachment,if you wouldn't mind....
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    Please take a better look at the examples in this thread, and look up Select Case in the VBA help. You structured the code incorrectly, it is much simpler:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A:A")).Cells
    Select Case oCell
    Case "Away", "Not at work"
    oCell.Interior.ColorIndex = 4
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End If
    End Sub

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formulate Conditional formatting (2K)

    Thank you Hans.....I did some more research as you suggested...and, I was able to modify your code and add a couple of more 'condition' or colors (following your model for armitageshanks)...works great...thanks again.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formulate Conditional formatting (2K)

    Good evening, Hans.....I am coming back to an earlier post that I did, and that others replied to....I took your code and some suggestions from armitrageshank and came up with the following:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("d22:s274")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("d22:s274")).Cells
    Select Case oCell
    Case "Away", "away", "Not working", "N/S", "n/s"
    oCell.Interior.ColorIndex = 4
    Case "Xvac", "xvac", "Xcon", "xcon"
    oCell.Interior.ColorIndex = 8
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End If
    End Sub

    .........this colors cells based on content.....I would like to epxand on it (eg: be able to put a name in a cell, followed by a period or an asterisk) and have it turn yellow....I have tried using code like Case "name"&"." etc etc but that is not working......you suggested that I should look at the SELECT CASE topics under VBA HELP......I went there but can't find any topics that look like what you mentioned...could the topic have a new or different name? Can you give me a link to it so that I can study this some more...thanks.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulate Conditional formatting (2K)

    To get help on Select Case:
    - Activate the Visual Basic Editor
    - Press Ctrl+G to activate the Immediate window.
    - Type select case
    - Press F1.

    > to put a name in a cell, followed by a period or an asterisk

    Do you mean that you want to color a cell if it contains a specific name (such as Dan) followed by a period or asterisk, or any name followed by a period or asterisk?

Page 1 of 2 12 LastLast

Posting Permissions

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