-1

I need to assign the value 1 or 0 based on the color of my cells (Red or Blue). I've heard alot about how to assign colors to values but not the other way. Im a beginner but I believe I will need to use an IF Statement in VBA for this, I haven't figured out how to assign a color as an input for an IF statement. Any help would be appreciated!

Thank You

Excel 2013

user5128509
  • 7
  • 1
  • 1
  • 4
  • 1) Record a macro while giving the two colors (red and blue) to a cell: this will give you the way VBA reads/writes color into a cell. 2) Create a custom function where you say "if color == this then return 1, else return 0"; 3) Insert the function into the cells you wish. – Matteo NNZ Jul 17 '15 at 17:52
  • Take a look at http://stackoverflow.com/questions/520570/return-background-color-of-selected-cell . This contains the VBA to get you the background color of a cell. – rajah9 Jul 17 '15 at 18:15
  • What version of Excel are you using? –  Jul 17 '15 at 19:55

1 Answers1

2

In the first example that follow I made the assumption that you want blue cells equal to 1 and red cells equal to 0.

Sub ifBlueMakeCellValueEQ1()
    Dim r      As Range
    Dim rCell  As Range

    Set r = Selection.Cells

    For Each rCell In r
        With rCell
            Select Case .Interior.Color
                Case Is = vbBlue
                    .Value = 1
                Case Is = vbRed
                    .Value = 0
            End Select
        End With
    Next

End Sub

to use this, first select a range of cells then run the macro.

If that works then ignore the remainder of this answer

If the values of your cells aren't changing to 1 or 0 it means your cell's colors aren't equal to excel's idea of blue and red (vbBlue and vbRed, respectively).

If you run into this problem do this: click on a 'blue' cell. Go to the VBE Immediate window, type the command "?activecell.interior.colorindex", hit enter. The integer that is returned should be used in the following code in place of {BLUECOLORINDEX}

Sub ifBlueMakeCellValueEQ1()
    Dim r      As Range
    Dim rCell  As Range

    Set r = Selection.Cells

    For Each rCell In r
        If rCell.Interior.ColorIndex = {BLUECOLORINDEX} Then rCell.Value = 1
    Next

End Sub
06chakin
  • 71
  • 5
  • I have one question what does the with add to the formula? What does it do. – user5128509 Jul 20 '15 at 19:42
  • @user5128509 aesthetics. I was able to refer to the object "rCell" without typing "rCell". [See MSDN article for more detail](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) – 06chakin Jul 20 '15 at 23:26