Excel & vb solution

Question

QUESTION: I'd like to do the following in Excel 2003 and 2007, and I believe it can only be achieved (mostly# in a VB script in Excel.

In a given cell #same column, and lets say the column depth is 30 cell rows, I want to be able to either have a pull down menu of colors to choose or enter a number in the cell for the following: Green, Yellow, Red, LightGreen, and Grey. This is beyond Excel's capability to do conditional formatting. At the end of 7 rows I want to average those rows. For example: Cell Cell Cell A1 A2 A3 GOAL OBJ STATUS Goal 1, Obj 1 Red

       Obj 2   Yellow
       Obj 3   Green
       Obj 4   G        Obj 5   LightGreen
       Obj 6   Green
       Obj 7   Yellow
     Average  ( I am assuming I can assign a value to the color like: 5-Green, 4-Yellow, 3-Red, 2-LightGreen, and 1-Gray). So the average of the above column would be 23/7 = 3.28 which would equal the average value of yellow assuming the following:

Green 5.00-4.00 Yellow 3.99-3.00 Red 2.99-2.00 LGreen 1.99-1.00 Gray 0-.99

Looking forward to your reply.

ANSWER: Sorry for the delay - I don't see where you get 23/7. The values total 28. The yellow values total 9. How do you average yellow-- 9 divided by 2 (since there are 2 yellows). I also don't understand your "range" of values -- how is Green 5.00-4.00 and where would that be used?

---------- FOLLOW-UP ----------

QUESTION: Using my assumption that the color numerations to use for averaging are 5-Green, 4-Yellow, 3-Red, 2-LightGreen, and 1-Gray (as above) I come up with 24, MY BAD.

Obj 1 Red 3 Obj 2 Yel 4 Obj 3 Grn 5 Obj 4 Gry 1 Obj 5 Lgn 2 Obj 6 Grn 5 Obj 7 Yel 4

         __
         24  So the row below Obj 7 would be the average of those sums, and in this case it is 3.42.  You would have to count each color, even if they show up more than once. So I'm thinking the script would have to make that an absolute.  Once we have that average, then we can use another absolute that says that anything averaged (such as the 3.42 above) falls into a range (as I have described above).  So 3.42 would end up as a yellow because the yellow range is 3.99 to 3.00.  I see what you are saying about green as we will never reach 5.0, so lets go with this:

Green 4.00 - 3.50 Yellow 3.49 - 3.00 Red 2.99 - 2.5 LGreen 2.49 - 2.0 Gray 1.99 - zero

Clear as mud? <smile>

Answer

You need to procedures - one is a function to do the average, but the other is a subroutine which must be run in order to color the average according to the range. Grey and Light green are subject to interpretation, ao you will have to use what I came up with or change it. In A8, enter =coloraverage(A1:A7) where A1:A7 are the various colors, then select A8 & run the Colorit procedure.

Function coloraverage(rg As Range) 'grey=12566463 'Lgreen=10092441

   For Each cl In rg
       Select Case cl.Interior.Color
           Case vbRed: n = 3
           Case vbYellow: n = 4
           Case vbGreen: n = 5
           Case 12566463: n = 1
           Case 10092441: n = 2
       End Select
       tot = tot + n
       i = i + 1
   Next
   coloraverage = tot / i

End Function Sub Colorit()

   Select Case ActiveCell.Value
       Case 3.5 To 4: col = vbGreen
       Case 3 To 3.49: col = vbYellow
       Case 2.5 To 2.99: col = vbRed
       Case 2 To 2.49: col = 10092441
       Case Is <= 1.99: col = 12566463
   End Select
   ActiveCell.Interior.Color = col

End Sub