Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have more than three conditional formatting need to setup, but I couldn't do it in excel 2003 because of the limitation. What codes can I use to put under Worksheet_Change event procedure, and it will do the following conditional formatting:

    1) cell volume is equal to 100%, the fill color of cell is green
    2) cell volume is equal to 95% - 99%, the fill color of cell is light blue
    3) cell volume is equal to 90% - 94%, the fill color of cell is yellow
    4) cell volume is below 94%, the fill color of cell is red

    Thanks in advance.

    Regards,

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    format all cells red, then use your first three items as Conditional Formats.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, I can't format all cells to red since some cells have volume for 0%. I will need it with no color.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello -
    If cell volume is 90%, 91%, 92%, 93% it would pass both conditions 3) yellow and condition 4) red. What color should it be?

    Is cell volume ever over 100%?

    Are cell volumes always rounded to a 2 digit percentage? For example, cell volume would never be 94.5% or 99.3%, etc.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Tim,

    If the cell volume between 90% to 94%, the color is yellow.
    If the cell volume below 94%, the color is red.

    No, the cell volume won't ever over 100%.

    Yes, all cell volumes always rounded to a 2 digit percentage.

    Thanks,

    Regards,

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Am I missing something? Lets say cell volume is 92%.

    92% is between 90% and 94%, therefore color is yellow.

    And

    92% is below 94%, therefore color is red.

    What color should 92% be?

    Thanks,
    Tim

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am sorry, Tim. You are right.

    between 90% and 94%, color is yellow.
    below 90%, color is red.

    Thanks,

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    West Virginia
    Posts
    17
    Thanks
    0
    Thanked 1 Time in 1 Post
    Have you tried something like this? Would this work?

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim icolor As Integer

    If Not Intersect(Target, Range("A1:A50")) Is Nothing Then

    Select Case Target
    Case 100
    icolor = 4
    Case 95 To 99
    icolor = 28
    Case 90 To 94
    icolor = 6
    Case 1 To 89
    icolor = 3
    Case Else
    'Whatever
    End Select

    Target.Interior.ColorIndex = icolor

    End If

    End Sub

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Tim.

Posting Permissions

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