Log in  \/ 
x
or
x
x
Register  \/ 
x

or

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”.
doc fill text based on color 1

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


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

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.
doc fill text based on color 2

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

=IF(GET.CELL(38,formula!A1)=10,"No",IF(GET.CELL(38,formula!A1)=3,"Yes","Neither"))

Into the Refers to textbox. Click OK.
doc fill text based on color 3

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

=Mycolor

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


Use a handy tool to auto fill 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.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

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.
doc fill text based on color 5

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

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.
doc fill text based on color 7

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.
doc fill text based on color 8 doc fill text based on color 9

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


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

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

=GetRangeColor(A1)

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.
doc fill text based on color 11


Sample file

Click to download sample file


Other Operations (Articles)

How to alternate row color based on group in Excel?
In Excel, to color every other row may be easier for most of us, but, have you ever tried to color the rows alternately based on a column value changes – Column A as following screenshot shown, in this article, I will talk about how to alternate row color based on group in Excel.

How to apply color gradient across multiple cells?
In Excel, we can easily fill background color to a cell or multiple cells, but, sometimes, we need the color be filled gradient as following screenshot shown, how could get the color gradient in a cell or across multiple cells in Excel?

How to apply color banded rows or columns in Excel?
This article will show you three methods of applying color banded rows or columns in Excel. Please do as follows.

How to apply shading to odd or even (alternative) rows/columns in Excel?
While designing a worksheet, many people tend to apply shading to odd or even (alternative) rows or columns in order to make the worksheet more visual. This article will show you two methods to apply shading to odd or even rows/columns in Excel.



  • 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...
kte tab 201905
  • 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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.