vbaexcelexcel-formulaformulas

Excel Formula Cell Based on Background color

I need a formula in EXCEL that place a number 1 in the cell next to the cell where the cell background is RED. See example below.

enter image description here

Is this possible at all without VBA?

Solution

  • Open the VBA editor and add a new module. Do this by going to the Developer tab and clicking Visual Basic. If you don't have the developer tab on the ribbon you will need to add it (do a quick Google search). Once the VBA editor is open, right click on the VBA project which has your workbook name on the left and insert a module.

    Place the following code into the new module:

    Function IsRed(rng As Range) As Integer
    IsRed = (rng.Interior.Color = vbRed) * -1
End Function

    then you can use the formula =IsRed(A1) to determine if A1 has a red background

    note: this uses the default red in the standard colours