Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Logic Based Color Display (Office 2003)

    Is it possible to program a cell fill color to change to one color or another based on certain criteria. For example, is it possible to setup up a stoplight alert system whereby the cell displays green if the difference between two numbers is between certain values, and red or orange when other criteria are met?

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

    Re: Logic Based Color Display (Office 2003)

    You can use conditional formatting:
    - Select cell D2.
    - Set the fill color to orange (this will be the default).
    - Select Format | Conditional Formatting...
    - Select 'Cell value is' from the first dropdown (the default), 'less than' from the second dropdown, and enter -5 in the box next to it.
    - Click Format...
    - Activate the Pattern tab and select red as fill color, then click OK.
    - Click Add >>
    - Select 'Cell value is' from the first dropdown (the default), 'greater than' from the second dropdown, and enter 5 in the box next to it.
    - Click Format...
    - Activate the Pattern tab and select green as fill color, then click OK.
    - Click OK.

    Note: you may want to change the number format so that negative numbers aren't displayed in red - red on red is invisible!

    See attached version.

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

    Re: Logic Based Color Display (Office 2003)

    Additional comment:

    It wasn't clear from your worksheet how you want the cell to be colored if its value is exactly equal to -5 or +5. Depending on your preference, you can use 'less than or equal to' instead of 'less than' and 'greater than or equal to' instead of 'greater than' in the Conditional Formatting dialog.

  4. #4
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logic Based Color Display (Office 2003)

    you cannot imagine how much I appreciate your responsiveness ....... and how silly I feel for not having found this myself ...... each time I pull down the format menu the option stares me in the face .... simple as pie she said .. yep, in the future I will most certainly try a little harder on the self help tread mill before conjuring the masters ... thanks again .......

  5. #5
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logic Based Color Display (Office 2003)

    Can the same funcionality enabling conditional formating applied to cells be applied to bar sets or individual bars in a horizontal bar chart as shown in the attached? Specifically, the values in HSE Column Z are represented in yellow colored bars of HSE Chart as positive values. Absolute values have been used to overcome chart limitations . However, for analytic purposes, it is necessary to know where the values fall within the same ranges set for the conditional formating in HSE Column Z. i.e. it is desired to produce the same conditional color changes in the HSE Chart Bars that is achieved in HSE Column Z cells.

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

    Re: Logic Based Color Display (Office 2003)

    Charts don't have conditional formatting, so you'll need to use a macro. Try the following macro (you'll have to run it again when the data change):

    Sub FillSeries()
    Dim i As Integer
    Dim intColorIndex As Integer
    For i = 1 To 20
    Select Case Worksheets("HSE Chart Data").Cells(11, i + 1)
    Case Is <= Worksheets(" HSE").Range("U1")
    intColorIndex = 3 ' red
    Case Is >= Worksheets(" HSE").Range("U2")
    intColorIndex = 4 ' green
    Case Else
    intColorIndex = 6 ' yellow
    End Select
    Worksheets("HSE Chart").ChartObjects(1).Chart. _
    SeriesCollection("ABS(DELTA %)").Points(i). _
    Interior.ColorIndex = intColorIndex
    Next i
    End Sub

  7. #7
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logic Based Color Display (Office 2003)

    In a few quick key strokes you have exceeded my capability with Excel. Back in the days of Quattro, I may have been able to figure out how to put your work to work. However, I must now revert to standard references for: where and how to record macros; and where and how to use them ....... I haven't a clue how to get the macro to operate on the chart .... so I have to ask for some very basic help to get this macro to function or ask for a quick reference already written to do the same thing .....

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

    Re: Logic Based Color Display (Office 2003)

    This macro is highly specific, so you should store it in the workbook itself. Here are step-by-step instructions:
    - In Excel, select Tools | Macro | Visual Basic Editor or press Alt+F11.
    - In the Visual Basic Editor, select Insert | Module.
    - Switch to your browser.
    - Select the code from my previous reply (from Sub up to and including End Sub).
    - Press Ctrl+C to copy the code.
    - Switch back to the Visual Basic Editor.
    - Press Ctrl+V to paste the code.
    - One way to run the macro is to put the insertion point anywhere in the code and to press F5.
    - To run the macro from Excel: select Tools | Macro | Macros..., select FillSeries from the list and click Run.

  9. #9
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logic Based Color Display (Office 2003)

    Ok ... this works great ..... let me see if I have the syntax sorted ............ I assume that the ( ' ) in the line means that anything following is a comment .... so I will write what I think each line does (accordingly) in an effort to show that I can extend the logic to other situtations (umm looks like the lounge lizard out to lunch & is making me wish I could go to dinner too ...... but I will finish this first):

    Sub FillSeries() ' name of macro
    Dim i As Integer ' establishes i's identity
    Dim intColorIndex As Integer ' this sets a vector called interColorIndex as an integer ...... did you make up this name ........ just don't know how this is selected or where name comes from
    For i = 1 To 20 ' sets loop for number of data columns in chart data table
    Select Case Worksheets("HSE Chart Data").Cells(11, i + 1) ' conditional routine for selecting which color to display
    Case Is <= Worksheets(" HSE").Range("U1") ' sets interColorIndex vector (11,i+1) to 3 if ("HSE Chart Data").Cells(11, i + 1) is in this range
    intColorIndex = 3 ' red
    Case Is >= Worksheets(" HSE").Range("U2") ' sets interColorIndex vector (11,i+1) to 4 if ("HSE Chart Data").Cells(11, i + 1) is in this range
    intColorIndex = 4 ' green
    Case Else ' ' sets interColorIndex vector (11,i+1) to 6 if ("HSE Chart Data").Cells(11, i + 1) is in this range
    intColorIndex = 6 ' yellow
    End Select ' ends selction routine
    Worksheets("HSE Chart").ChartObjects(1).Chart. _ ' id's the object on which the macro acts - don't know how to find name of objects - change tab name & object id for modification
    SeriesCollection("ABS(DELTA %)").Points(i). _ ' id's the series for changing color --- in any case, only have to change series name, all else can remain the same
    Interior.ColorIndex = intColorIndex ' must be inside knowlegde for series fill color coding ........ in any case this does not need to be modified ... i think !!
    Next i ' round and round until i = 20
    End Sub

    Will you please comment ........ the only things I am not sure about is how to ID an object name and where the name " intColorIndex " comes from ....... where may I find the names of objects and where may I find a list of variable names for Excel elements like intColorIndex ??

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

    Re: Logic Based Color Display (Office 2003)

    The lines beginning with Dim at the beginning are declarations. They tell Visual Basic that I am going to use variables named i and intColorIndex, both of type Integer (i.e. they can contain whole numbers from -32,768 to +32,767). I chose the names i and intColorIndex, I could have named them John and Mary instead.

    The lines

    Worksheets("HSE Chart").ChartObjects(1).Chart. _
    SeriesCollection("ABS(DELTA %)").Points(i). _
    Interior.ColorIndex = intColorIndex

    form one instruction; the space and underscore _ at the beginning of the first two lines tell Visual Basic that the instruction isn't finished yet, and that it will be continued on the next line.
    I did this only to increase readability, it is not essential. I could also have written it

    Worksheets("HSE Chart").ChartObjects(1).Chart.SeriesCollection("AB S(DELTA %)").Points(i).Interior.ColorIndex = intColorIndex

    It says:
    - Take the sheet named HSE Chart.
    - Take the first (and in this example only) chart object on the sheet.
    - Take the actual chart in this chart object.
    - Take the data series named ABS(DELTA %).
    - Take the i-th point (bar) of this data series
    - Take the interior of this bar.
    - Set the ColorIndex of this bar to the value of intColorIndex, which has been set to 3, 4 or 6 higher up in the code.

    Click in the word ColorIndex and press F1 to get help on this keyword. ColorIndex is a number that specifies a color from Excel's color palette:
    1 = black
    2 = white
    3 = red
    4 = green
    5 = blue
    6 = yellow
    etc.

    You can also click in words such as Worksheets, ChartObjects, SeriesCollection etc. and press F1 for help. This won't work if you click in the word intColorIndex and press F1, because intColorIndex is a name I made up.

  11. #11
    Lounger
    Join Date
    Jun 2007
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logic Based Color Display (Office 2003)

    what a guy .. thanks ....... I already dummied up another sheet and chart set and got the bar colors to change as expected ..... the last time I programmed anything was with Fortran 4 and although I am a little rusty (an understatement) .... things are actually making sense ..... have a great day (night or whatever it is where you are) ... I'm going to dinner .....

Posting Permissions

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