Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2005
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting (2003)

    Hi there
    I'm working on a spreadsheet and I've been asked to create conditional formatting with 6 formats. 6 colours.
    Number 1 green - Number 2 red - etc. (pretty basic)
    At the moment I have 3 colours + white..

    I've checked previous posts but my knowledge of VB is not good enough to change formula.

    What I need is from A4:AG4 a simple formula that does what I need.

    When I told my boss "you can only have 3 criteria" he said "I bet you can do it". So here I am struggling away.

    Your help is appreciated

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

    Re: Conditional formatting (2003)

    See <post:=557,373>post 557,373</post:> for an example of how to use VBA to specify many colors for conditional formatting.

  3. #3
    Lounger
    Join Date
    May 2005
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (2003)

    I've changed the range to suit my spreadsheet but the first line is yellow indicating something isn't right. I'm sorry but not so bright as many on here that can write these wonderful macros
    Thanks

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A4:Ag4")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A4:Ag4")).Cells
    If IsDate(oCell) Then
    Select Case (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

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

    Re: Conditional formatting (2003)

    You're missing an End Sub, and I don't think you want to test whether the value is a date. Here is a somewhat shorter version that colors the values 1 through 6.
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A4:AG4")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A4:AG4")).Cells
    Select Case (oCell)
    Case 1
    oCell.Interior.ColorIndex = 4
    Case 2
    oCell.Interior.ColorIndex = 3
    Case 3
    oCell.Interior.ColorIndex = 8
    Case 4
    oCell.Interior.ColorIndex = 6
    Case 5
    oCell.Interior.ColorIndex = 7
    Case 6
    oCell.Interior.ColorIndex = 15
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End If
    End Sub
    </code>
    If you want to color the text instead of the background, change oCell.Interior.ColorIndex to oCell.Font.ColorIndex throughout the code.

  5. #5
    Lounger
    Join Date
    May 2005
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (2003)

    You are so clever !!!! That worked wonderful
    Thank a lot..

Posting Permissions

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