Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with conditional formatting

    Hi there,

    I need to be able to color code a cell based on it's value. The conditions behind the coloring output depend on more than 2 scenarios that I am used to with this feature. For instance, the output can be the following:

    Under threshold = red
    Meeting threshold = yellow
    Between threshold and target = yellow/green
    Target = green
    Between target and opportunity = green/blue
    Opportunity = blue

    Is it possible to write a formula or apply conditional formatting to bring back these colors based on the scenarios indicated above?

    Thanks!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You can set multiple conditions if you have XL2007 or better (assuming A1 is the cell you are working on):
    =A1=Opportunity
    =AND(A1>Target,A1<Opportunity)
    =A1=Target
    =AND(A1>Threshold,A1<Target)
    =A1=Threshold
    =A1<Threshold
    [And Possibly one for :
    =A1>Opportunity
    or
    =A1>=Opportunity]

    Each one would have a set formatting.

    If you have a version pre-XL2007 you are limited to 3 conditions which allows for 4 colors (1 explicit + 4 conditional), so if you need this many, you would have to write a macro from the change event (and eliminate undo on the page).

    Steve

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Here is some code that can be added to the Worksheet object to change Cell F1 (for example):

    Change cell and colors as desired (This assumes that you have a named formulas in the worksheet for the limits)
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("F1")) Is Nothing Then
        With Range("F1")
          Select Case .Value
            Case Is = Range("Opportunity")
              .Interior.Color = RGB(0, 0, 255) 'blue
            Case Is > Range("Target")
              .Interior.Color = RGB(0, 125, 125) 'green-blue
            Case Is = Range("Target")
              .Interior.Color = RGB(0, 255, 0) 'green
            Case Is > Range("Threshold")
              .Interior.Color = RGB(125, 255, 0) 'yellow-green
            Case Is = Range("Threshold")
              .Interior.Color = RGB(255, 255, 0) 'yellow
            Case Is < Range("Threshold")
              .Interior.Color = RGB(255, 0, 0) 'red
          End Select
        End With
      End If
    End Sub

  5. #4
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve!

    I do have Excel 2010, so I think I can do what you mentioned previously. Though. I'm not sure what you mentioned will work as the coloring needs to be somewhat of a percentage basis. See below.

    Thanks so much! Your help is greatly appreciated.

    Metric BELOW THRESHOLD THRESHOLD TARGET OPPORTUNITY CURRENT
    (RED) (YELLOW) (GREEN) (BLUE)
    Cash from Operations 30.0 50.0 60.0 57.0 Should be green/blue. More blue than green, however.
    Capital Expenditures 130.0 115.0 90.0 135.0 Should be a percentage red/yellow. More yellow than red, however.
    Operating Expenditures 15.0 10.0 5.0 10.0 Should be green
    Net Income 10.0 25.0 35.0 30.0 Should be 50% green and 50% blue

  6. #5
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, my response didn't post as I seen it on my screens so that may be a bit confusing. Try this:

    Metric
    BELOW THRESHOLD (RED)
    THRESHOLD (YELLOW)
    TARGET (GREEN)
    OPPORTUNITY (BLUE)

    1. Cash from Operations
    Threshold = 30.0
    Target = 50.0
    Opportunity = 60.0
    Outcome = 57.0 (Should be green/blue. More blue than green, however).

    2. Capital Expenditures
    Thres = 130.0
    Target = 115.0
    Opportunity = 90.0
    Outcome =135.0 (Should be a percentage red/yellow. More yellow than red, however).

    3. Operating Expenditures
    Thres = 15.0
    Target = 10.0
    Opp = 5.0
    Outcome = 10.0 (Should be green).

    Etc.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Excel Cond Formatting only has a 3 color gradient, not a 4 color. The attached uses the 3 color gradient to display the gradient with yellow/green/blue percentages. Anything below the threshold is red, there is no gradient. So this is the compromise. You can change the colors of each setpoint if desired if you did not want the pure yellow, green, and blue

    If you must have a 4-color gradient (and have a gradient for the red to yellow), it would have to be a macro if the status us entered, it could be worksheet change, if a formula, it would have to be a calculation macro). There would also need to be a target value for 100%red to calculate the percentage of red/yellow.

    Steve
    Attached Files Attached Files

  8. #7
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Steve! I will take a look tonight.

  9. #8
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting

    Hi Steve,
    So I took a look at your excel file and the formulas seem to be written correctly, however, the gradient fill comes in as a whole color versus a vertical gradient fill. I was hoping to see it as a vertical gradient fill. I illustrated what I mean in powerpoint , though I cannot seem to attach it. The upload on the attachment always times out.
    Karla

  10. #9
    New Lounger
    Join Date
    Jan 2014
    Location
    St. John's, Newfoundland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your time, by the way. Do you know how I could attach this file another way? Is this usually an issue for some?

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I am not sure that multiple gradient fills could be done with conditional formatting in the way I suspect you mean. I am already using a 3-color gradient to do what this is, it would require a macro. If a macro you will have to be more detailed on the structure (as well as the limit for when it should be 100% red).

    I am not sure what issues you are having attaching a file. Try attaching an excel file and just embed the powerpoint illustration on a sheet.

    Steve

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I hadn't heard back from you on your requirements, but I had some time and took a stab at it. i did it with conditional formatting using several databars and fills in the cell.
    I used the 4 rows you had. I added a column for the value for where it will be 100% red, since that is required to calculate the yellow/red percentages.

    The biggest issue is that the databars require absolute referencing, so if you copy the formatting you must adjust the databar formulas for the correct row to match the cells. {which means editing 6 conditions with 2 limits each and 7 instances of the row in each formula, so a lot of edits)

    The databar formulas and fill will change dependent on whether the series is "bigger is better" or "smaller is better", so you can change the value or the limits of each/ The type (larger/smaller is better) is determined by comparing the values in B&E, non other ones are checked so if those limits are not a increasing or decreasing series you may get odd results.

    I hope this is what you are after...

    Steve
    Attached Files Attached Files

Posting Permissions

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