Forum Discussion
Giving a colored cell a numerical value
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
These are just metrics, not sensitive information. But this is the dataset I was given and am working with.
For example, Lines 16-19 (four rows), one of the column will be colored based on today's patient volumes. In this example, I need B16=1, E17=4, E18=4, and B19=1, (because I need green=1, orange=2, yellow=3, red=4).
So then to tally the colored values, it should be 10.
As I type this, it sounds confusing, but basically I need the color to equal a numerical value, if that makes any sense.
Have B16, E17 etc. been colored manually or do you use conditional formatting rules to color them?
They are colored manually. Our units on the hospital floors report daily during our huddles, reason why it’s colored manually.
Thanks, this requires VBA, so it will work only in the desktop version of Excel for Windows and Mac.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the module:
Function Score() As Double Dim colors(2 To 5) As Long Dim r As Long Dim c As Long Application.Volatile For c = 2 To 5 colors(c) = Cells(15, c).Interior.Color Next c For r = 16 To 19 For c = 2 To 5 Score = Score - (c - 1) * (Cells(r, c).Interior.Color = colors(c)) Next c Next r Score = Score / 4 End FunctionSwitch back to Excel.
Enter the following formula in the cell where you want the score:
=Score()
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Remark: if you change the color of a cell in B16:E19, the formula result will not be updated automatically. This will happen whenever Excel recalculates. You can force this by pressing F9.