How to change value based on cell color in Excel?

This article is talking about changing cell value automatically based on its background color in Excel. For example, if cells filling with red background color in selection, then populate these cells with number 1, and for the blue background color cells, fill in number 0.

Change value based on cell color with VBA code

Change value based on cell color with VBA code

You can run the below VBA code to change value based on cell color in Excel. Please do as follows.

1. Select the range that you need to change values based on background color, then press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy and paste the below VBA code into the Module window.

VBA code: Change value based on cell color in Excel

Sub ChangeValueBasedOnCellColor()
    Dim rg As Range
    Dim xRg As Range
    Set xRg = Selection.Cells
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .Interior.Color
                Case Is = 255 'Red
                    .Value = 1
                Case Is = 15773696 'Blue
                    .Value = 0
            End Select
        End With
    Next
    Application.DisplayAlerts = False
End Sub

3. Press the F5 key to run the code, then you can see all red cells in selected range are filled with number 1, and the blue cells are filled with number 0 as below screenshot shown.