Value based on color formatting of cell

[mluetkem]

I've got a situation where I have a range of cells, say A1:E1, which are used as a scoring field (i.e. values 1-5 respectively). The score of an event is indicated by filling one of the 5 cells with a color (light yellow). I need a formula in F1 which will tell me the numerical value of the cell which was selected by being filled with "light yellow" for the score (i.e. 1, 2, 3, 4 or 5).

Thanks.

Mike

[Paul]

You could use the following function in a module:

Please Login or Register  to view this content.

To return the value in A1:E1 with a light yellow background, in cell F1 you could type:

=getYellow(A1:E1)

It could be used for any range, of course, not just A1:E1. One thing to note.. if you make any changes, e.g. change the light yellow cell or change any cell values, you'll need to press F9 to re-calculate the entire sheet, which will update the function's result.

To add a module:
1. Press ALT+F11 to open the VB Editor, then ALT+I+M to insert a module.
2. Copy the above function and paste it into that module.
3. Close the VB Editor window