Use formula or defined function to auto fill text based on the cell filled color in Excel

Supposing there is a list of cells filled with background color red or green, now you want to fill texts based on the cell filled color. For instance, as below screenshot shown, fill cell filled with red color “Yes”, fill cell filled with green color “No”, neither of red nor green, fill text "Neither".

Use formula to auto fill text based on the cell filled color

Use a handy tool to auto fill text based on the cell filled color

Use defined function to auto fill text based on the cell filled color in another column

Sample file

To use a formula to auto fill cell with a specific text based on the cell filled color, you need to define a name firstly.

1. Select a cell, supposing cell A1, click Formulas > Define Name in the Defined Names group.

2. In the New Name dialog, type Mycolor in the Name box, and enter this formula

Into the Refers to textbox. Click OK.

Formula!A1 indicates the cell A1 in the sheet named formula, 10 and 3 is the color index, you can change them as you need. More color index, please refer to this site: http://dmcritchie.mvps.org/excel/colors.htm

Now, in the cell which is filled with color, you can type this formula

Press Enter key, then you will return the text based on the cell filled color.

With the define name method, you need to retype the formula in cell every time which is troublesome. Here with Kutools for Excel, a collection of Excel tools, its Select Cells with Format utility can quickly select all cells in one background color, then you can type the specified text into them in bulk.

After free installing Kutools for Excel, please do as below:

1. In two cell you do not use, and fill the background color separately. Supposing, in this case, I want to fill text “Go” to the cells in blue color, fill text “Stop” to the cells in orange color, I will fill color orange and blue into cell A1 and B1.

2. Then select the cells that you want to enter text based on the filled color, click Kutools > Select > Select Cells with Format.

3. In the Select Cells with Format dialog, click Choose Format From Cell button, and then in the popping out dialog, choose A1, which was filled with the color you want to find cells based on.

Click OK to back to the main dialog, uncheck all except the Fill checkbox, and untick the Ignore the blank cells option. Click Ok > Yes to select all cells filled with blue color.

4. Now type the specific text in the formula bar, press Ctrl + Enter key to fill all selected cells with the same text.

If you want to fill text based on the cell filled color in another column, you can use the define function.

1. Press Alt +F11 keys to enable the Microsoft Visual Basic for Applications window.

2. Click Insert > Module in the window to insert a blank script, then copy and paste below code.

VBA: fill cell based on filled color in another cell

Function GetRangeColor(xRg As Range)
'UpdatebyKutools20191125
    If (xRg.Count > 1) Then
       GetRangeColor = "Only work for single cell"
    End If
    Select Case xRg.Interior.Color
    Case RGB(255, 0, 0)
        GetRangeColor = "Play"
    Case RGB(0, 255, 0)
        GetRangeColor = "Stop"
    Case Else
      GetRangeColor = "Neither"
    End Select
End Function

In the code, RGB(255, 0, 0) and RGB(0, 255, 0) are the color index, you can change them as you need. More color index, please refer to this site: http://dmcritchie.mvps.org/excel/colors.html

3. Save this code and close the window and back to sheet. Then in a cell type formula

Press Enter key, then drag fill handle over the cells to fill this formula.

A1 is the cell that you want to fill text based on its filled color.

Click to download sample file

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Read More... Full Features 30-Day Free Trial... Purchase... 

---

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!

Read More... Full Features 30-Day Free Trial... Purchase...