Excel Conditional Formatting

[Sandman007]

I am creating a spreadsheet with conditional formatting as a feature. However I have a small bump in the road. I am looking to create some functionality in my spreadsheet that will do the following:

I have lets say 10 cells. These ten cells make up 1 cell (i.e. they sum to one cell). Each of these 10 cells will conditional format based on set formatting criteria of green, yellow, and red shading. Green shading is considered good while yellow and red are considered bad. If one of my ten cells is either yellow or red I want the cell that sums these 10 cells to turn either red or yellow. For example 9 of the 10 cells are green, bu t only one of the 10 cells is red. I want the cell that sums all 10 cells to be red. Or the other variations could be if I have 9 green and 1 yellow I want the cell that sums the 10 to be yellow or if there are 5 green, 4 yellow, and 1 red I want the cell that sums the 10 to be red.

I don't think I can accomplish this by just using the conditional formatting feature that is embedded in the format drop down; rather I think there might need to be some VB.

Please Help!

 

[mscallisto]

The following assigns the color value to a cell, you will need to adjust the "For" range to suit your purpose.

Example of color values are: 5=blue, 6=yellow, 9=red etc.

Hope this helps
Much Luck
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SortColor()
'
' Disp Color Value Macro
' Macro recorded 3/26/2001 by Stewart Mills
'
' Keyboard Shortcut: Ctrl+s
ColorCode = 1 ' Column number to store results
TextColumn = 2 ' Column number that has colored text

For IntR = 1 To 20
ActiveSheet.Rows(IntR).Columns(ColorCode) = ActiveSheet.Rows(IntR).Columns(TextColumn).Font.ColorIndex
Next IntR

End Sub

 

[logius]

I'm working on a formula version of this, but having problems. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[Sandman007]

I really need this to work without having to use a keyboard shortcut. It needs to happen as data is being populated into the spreadsheet. Thanks.

 

[JustinEzequiel]

Sandman,

Got something working using only conditional formatting.

Can send you the WorkBook if you like.

If you don't mind posting your E-mail address that is.

 

[tsuji]

Hello, Sandman007.

I can understand your preoccupation to use conditional formatting on the cell with Sum. Below is a solution. I make some assumptions. They can be easily be adopted to your application.

[1] Suppose that the Range A1:A10 contains the numeric, say betweeb 0 and 100.
[2] Conditional formatting as applied to A1:A10 is supposed to be the following. Take A1 as an instance and format paint to other.
Conditional format of A1:
[2.1] Condition 1 : "Cell Value Is"
A1 >= 80 (or pointing to some other cell)
Pattern Green
[2.2] Condition 2 : "Cell Value Is"
AND( A1 >=60, A1<80)
Pattern Yellow
[2.3] Condition 3 : &quot;Cell Value Is&quot;
A1 <= 60
Pattern Red
[3] Suppose the cell A12 :
=SUM(A1:A10)
[4] Now at some freely available cells, I suppose here being A14 and A15, you can create the array formula with boolean value :
A14 : {=OR($A$1:$A$10 < 60)}
A15 : {=OR($A$1:$A$10 < 80)}
[5] With the help of A14 and A15, you can now conditional format A12 (the sum cell) with ease.
[5.1] Condition 1 :
&quot;Formula Is&quot; =$A$14
Patterm Red
[5.2] Condition 2 :
&quot;Formula Is&quot; =$A$15
Pattern Yellow.
[5.3] Note that the above makes use of condition 1 take precedent over condition 2 so that the formula in A15 can be formulated as one side limited by 80 rather than restricting to range (60 to 80).

With the above construction, the conditional format of the cell with sum value is achieved. The general idea is clear and should not be restricted to the particulars of the assumptions made above.

regards - tsuji