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.
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!