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".
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.
|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.
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.