Giving a colored cell a numerical value

Question

Hi all, I'm new to the discussion board, but I am stumped on trying to write an IF statement on what I want to do.

What I want to do, is give a value based on the colored cell, for example, green = 1, yellow = 2, orange = 3, red = 4. Each cell has text in it already (non-numerical texts), but they're highlighted based on the urgency level for our institution.

Given there are rows with a variety of green/yellow/orange/red cells, I want a cell that calculates them based on the value above, divide that sum by 4, and condition that cell to a color based on the end result.

Overall, my question is it possible to give a color a value? I understand I can condition a cell to a color based on the value, but this issue is entirely different.

Thanks,

TD

hansvogelaar · Answer

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?

tuand · Answer

&nbsp;These are just metrics, not sensitive information. But this is the dataset I was given and am working with.&nbsp;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.

hansvogelaar · Answer

Have B16, E17 etc. been colored manually or do you use conditional formatting rules to color them?

tuand · Answer

They are colored manually. Our units on the hospital floors report daily during our huddles, reason why it’s colored manually.

hansvogelaar · Answer

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 &gt; 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 Function
Switch 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.

Forum Discussion

Giving a colored cell a numerical value

5 Replies

Resources