Excel: If the font color is red then return a specific text or do other operations
In real-world Excel use, font colors—especially red—are commonly used to indicate warnings, exceptions, failed validations, or high-priority notes. While it’s easy to apply such colors manually or via conditional formatting, it’s not straightforward to use formulas to detect and act on font colors. For example, you might want to return a custom message, highlight a related cell, or adjust formatting based on whether a cell’s text is red—as shown below.
This article introduces several practical solutions to handle operations based on red-font text in Excel. These methods range from VBA-based detection and conditional formatting to built-in tools like Find & Replace. We’ll also explore use-case suitability and common pitfalls to help you choose the best method.
If font color is red then return specific text in another cell
When you want to return specific text (such as "Fail" or "Pass") in another cell depending on whether a referenced cell's font color is red, a direct formula will not work because Excel functions cannot test font color by default. To achieve this, you can use a custom VBA function, also known as a User Defined Function (UDF). This is suitable if you need to dynamically display results that are always up to date with font color changes. Be aware that all users of the workbook need macros enabled for the function to work.
1. Press Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications editor window.
2. Next, select Insert > Module. In the opened module window, copy and paste the following VBA code:
VBA code: If font color is red then return specific text
Function FontColorisRed(Rng As Range)
'Updateby ExtendOffice
Application.Volatile
If Rng.Font.ColorIndex = 3 Then
FontColorisRed = "Fail"
Else
FontColorisRed = "Pass"
End If
End Function
3. Close the VBA editor. Now, in a worksheet cell, enter the following formula (for example, in cell C2): =FontColorISRed(B2) and then drag the fill handle down to apply the formula for other rows. The result will instantly indicate "Fail" if the referenced cell's font color is red.
If the formula does not seem to update after you change the font color in the referenced cells, try pressing F9 or re-entering the formula to refresh the result. Keep in mind that, as with all VBA solutions, your file must be saved as a macro-enabled workbook (.xlsm), and macros must be enabled to use this feature.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
If font color is red then highlight the cell
When you want to highlight cells whose font color is red, for enhanced visual management or further action, you can use a combination of a VBA function and Excel's Conditional Formatting. This solution is effective when you want visual emphasis on certain cells and also want to combine it with existing formatting rules. Be sure to handle macro security settings appropriately if you plan to share the file, as macros are required for this approach.
1. Open the VBA editor by pressing Alt + F11.
2. Click Insert > Module and paste the following VBA code into the module window:
VBA code: If font color is red then highlight the cell
Function HighlightRedFont(pRg As Range) As Boolean
'Updateby ExtendOffice
Dim xRg As Range
Dim xBol As Boolean
xBol = False
For Each xRg In pRg
If xRg.Font.Color = vbRed Then
xBol = True
End If
Next
HighlightRedFont = xBol
End Function
3. Close the VBA editor and return to Excel. Go to Home > Conditional Formatting > New Rule, as shown below:
4. In the New Formatting Rule dialog box, perform the following steps:
- Under Select a Rule Type, choose Use a formula to determine which cells to format.
- Enter this formula in the formula box (for example, applying to B2): =highlightredfont(B2)
- Click the Format button to set your desired highlighting options.
5. In the Format Cells dialog, go to the Fill tab and select the highlight color for the cells. Click OK to confirm your choice:
6. Click OK twice (once for each dialog) to apply the rule. Cells with the red font will now be automatically highlighted according to your settings, as illustrated here:
If the conditional formatting does not update after changing a font color, try editing the affected cell or pressing F9 to refresh. Remember, this method depends on the custom VBA function being available and macros enabled.
If font color is red then change the font color
You may need to convert all text in red font to a different font color, either to unify the formatting or mark data more appropriately. There are several solutions for this requirement in Excel, including built-in Find and Replace and third-party add-ins like Kutools for Excel. Each approach has its specific advantages and things to keep in mind.
If font color is red then change the font color with Find and Replace function
Excel's Find and Replace feature supports searching and replacing by formatting, allowing you to select all cells with a specific font color (like red) and change them to another color efficiently. This method is good for quick operations across a worksheet or range and does not require macros or add-ins.
1. Select the range where you wish to change the font color, then press Ctrl + H to open the Find and Replace dialog. Click Options >> for more settings, as shown:
2. In the expanded dialog, do as follows:
- Leave the Find what and Replace with fields blank to focus on formatting.
- Next to the Find what box, click Format > Choose Format From Cell, then click a cell with the red font you want to target.
- Beside Replace with, click Format > Format to open the Replace Format dialog.
3. In the Replace Format dialog, under the Font tab, choose your desired replacement color from the Color dropdown. Confirm your settings:
4. Click OK to exit the Replace Format dialog. Finally, in the Find and Replace dialog, click Replace All. All cells with the identified red font will immediately change to your chosen new font color:
This method is especially useful when you want a one-time bulk operation without enabling macros. However, if your workbook regularly changes font color as part of a workflow, consider using an automated or selective solution.
If font color is red then change the font color with a useful feature
If you have Kutools for Excel installed, its Select Cells with Format feature allows you to quickly select all cells with the exact same font color (such as red) and then apply a new font color in bulk. This method is more flexible than the basic Find and Replace, because you can limit the selection to font color only or include/exclude other specific attributes as needed. Kutools provides a user-friendly interface for these operations, making it ideal for users who frequently manage rich formatting in large datasets.
After installing Kutools for Excel, proceed as follows:
1. Select the range to operate on, then go to Kutools > Select > Select Cells with Format as shown below:
2. In the Select Cells with Format dialog, click Choose Format From Cell, then select a cell with red font. You can adjust which formatting properties to match—in this case, keep only Font Color checked to match all cells that use this red font, regardless of other formatting.
3. Click OK. In the prompt box, click Yes to continue:
4. All cells with the red font color are now selected at once. With these cells selected, you can manually change their font color using the Excel font color dropdown as needed.
This approach is particularly efficient for large data ranges or situations requiring selective formatting adjustment based on color alone. When using this function, take care to verify that only the desired formatting attributes are selected—otherwise, you may omit cells that appear visually similar but have minor formatting differences.
Tip: Kutools also offers batch processing and undo features that can help prevent errors and quickly revert changes if you make a mistake during selection or formatting operations.
If you encounter unexpected results or the desired cells are not being selected, double-check that only the correct formatting attributes (such as font color) are checked in the dialog. Also, ensure that you are working within the targeted data range.
Besides the above methods, there are other ways to process data based on font color in Excel:
- VBA batch processing code: If you often need to operate on a large dataset, a custom VBA script can iterate all cells and perform actions when red font is detected. This is flexible for bulk formatting, marking, or logging matching cells, but requires basic VBA programming knowledge.
- Manual review combined with sorting: Use Excel's Sort by Cell Color or Font Color (available in Excel2007 and above) to group red font data together for easier review or editing. This is especially suitable for one-time tasks or when macros/add-ins are not allowed.
No matter which approach you use, always save your work beforehand and ideally try out methods on a sample copy of your data, particularly when using batch operations or macros. Routine troubleshooting includes pressing F9 to refresh calculations and verifying macro settings under Excel's Trust Center if VBA-based approaches do not function as expected.
In summary, operations based on font color in Excel require more than just built-in formulas. Choosing the right tool—VBA, Excel built-ins, or add-ins like Kutools—will depend on your workflow frequency and complexity. Whichever method you choose, always double-check color codes, formatting attribute selections, and macro security to ensure efficient and accurate results.
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...
- 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!
