Forum Discussion

TuanD's avatar
TuanD
Copper Contributor
Apr 15, 2025

Giving a colored cell a numerical value

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 = ...
excel
TuanD's avatar
TuanD
Copper Contributor
Apr 15, 2025

 

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.

HansVogelaar's avatar
HansVogelaar
MVP
Apr 15, 2025

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

  • TuanD's avatar
    TuanD
    Copper Contributor
    Apr 15, 2025

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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 15, 2025

      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 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.

Resources