Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    More that 3 conditional formats (Excel XP SP2)

    Dear loungers,

    I wonder if someone has a peice of code I can steal. I need a macro that will be run when necessary to format the cells in a range - I have more than three formats. I can't use an event since the importing of the data is a rather messy process and not based on data entry, I therefore need something I can run as required. If you have something and can explain where I put it I'd be greatful

    thank you.............. margie

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

    Re: More that 3 conditional formats (Excel XP SP2)

    Here is an example that you can use as a starting point:

    Sub FormatCells()
    Dim oCell As Range
    ' Only format cells in columns A through C
    For Each oCell In Intersect(Range("A:C"), ActiveSheet.UsedRange)
    ' Look at the cell value
    Select Case oCell.Value
    Case Is < 0
    oCell.Interior.ColorIndex = 3
    Case 1 To 4
    oCell.Interior.ColorIndex = 5
    Case 5 To 9
    oCell.Interior.ColorIndex = 9
    Case 10 To 15
    oCell.Interior.ColorIndex = 4
    Case Is > 15
    oCell.Interior.ColorIndex = 7
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End Select
    Next oCell
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: More that 3 conditional formats (Excel XP SP2)

    Dear Hans,

    Thank you Liz (whose account I'm temporarily using) said you were very helpful, I'll have a go. I assume I create a module and paste the code in then adjust it?

    Margie

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

    Re: More that 3 conditional formats (Excel XP SP2)

    Yes:
    - From Excel, press Alt+F11 or select Tools | Macro | Visual Basic Editor.
    - Select Insert | Module.
    - Paste the code into the module.

    Items to edit are:
    - The range that you want to format.
    - The conditions in the Case ... lines.
    - The color indexes in the oCell.Interior.ColorIndex = ... lines.
    - Instead of setting the fill color using oCell.Interior, you can set the text color using oCell.Font.

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: More that 3 conditional formats (Excel XP SP2)

    Thank you it works well, perhaps you would mind telling me this which will help me finish it off. Can I use Like in a Case staement?

    margie

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

    Re: More that 3 conditional formats (Excel XP SP2)

    No, you can't use Like in a Case statement, you'll have to handle this separately, for example:
    <code>
    If oCell.Value Like "Inter*" Then
    ...
    ElseIf oCell.Value Like "Global*" Then
    ...
    End If
    </code>
    You can combine this with a Select Case statement:
    <code>
    If oCell.Value Like "Inter*" Then
    ...
    Else
    Select Case oCell.Value
    Case "Global"
    ...
    Case "World"
    ...
    End Select
    End If</code>

  7. #7
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: More that 3 conditional formats (Excel XP SP2)

    Oh, I see. Thank you very much.
    margie

Posting Permissions

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