Assign a value to a colored cell

[neholsti]

what I’m trying to do is a little out of my league. I have a spreadsheet with a whole bunch of cells with different colors (blue, green, yellow, and red) and these colors show at what stage a project is in. I wanted to make a macro that would assign each color a number (blue = 5, green = 4, yellow = 3, and red = 1) for computing the progress of each project. I really have no idea where to start, I know that you need to relate the value with the color index number, but that’s about it. If you have time and can help me I greatly appreciate it

 

[pboltonchina]

Try this. It will do the first 6 Excel colours, you can add to it or alter it to suit the colours you are using.

Option Explicit
Sub NumberColurValue()
    Dim R As Range
    With ActiveSheet
        For Each R In ActiveSheet.UsedRange
            Select Case R.Interior.ColorIndex
                Case 1
                R.Interior.ColorIndex = 1
                R.Value = 1
                Case 2
                R.Interior.ColorIndex = 2
                R.Value = 2
                Case 3
                R.Interior.ColorIndex = 3
                R.Value = 3
                Case 4
                R.Interior.ColorIndex = 4
                R.Value = 4
                Case 5
                R.Interior.ColorIndex = 5
                R.Value = 5
                Case 6
                R.Interior.ColorIndex = 6
                R.Value = 6
            End Select
        Next R
 End With
End Sub