Logic Based Color Display (Office 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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 …….

Reply | Quote

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.

Reply | Quote

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(“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

Reply | Quote

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 …..

Reply | Quote

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.

Reply | Quote

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(“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 ??

Reply | Quote

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(“ABS(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.

Reply | Quote

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 …..

Reply | Quote