If Cell Color Is Red Then Execute Different Functions in Excel

Sometimes, we like to highlight similar types of data or somehow related data in a huge dataset. We not only highlight them to understand their similarity just by looking but also like to have them sorted in terms of working with those data. In this article, I will try to describe 5 practical cases of how to execute Excel functions if the cell color is red. I hope it will be helpful for those who are facing problems dealing with it.

For more clarification, I am going to use a dataset of football players’ salary information in the Player Name, Team, and salary columns.

Download Practice Workbook

If Cell Color Is Red Then You Can Execute 5 Following Operations in Excel

1. Counting Red Color Cells

In a dataset where some cells are highlighted in red color, we can easily count them. We can count the number of red cells using the COUNTIFS function. We can do it in 2 simple steps.

1. Define Name
2. Applying the COUNTIFS Function

Steps:

• Go to Formulas.
• Select the Define Name option from the ribbon.

An Edit Name wizard will appear.

• Set a name in the Name section (i.e. Identify_Red).
• Next, input the following formula in the Refers to section.

Here, 63 returns the fill (background) color of the cell. COUNT! refers to the sheet name.  $B15 is the cell address of the first cell to consider in Column B.

• Then, press OK.

• Now, create a new column (i.e. Color Code) to have the code number of the color.
• Apply the following formula in the E5 cell of the Color Code

Here, I have mentioned the defined name.

• Press ENTER to have the color code.

• Use Fill Handle to AutoFill the rest columns.

• Now, input the following formula to have the Number of Red Cells.

Here, the COUNTIFS function counts the red cells in cells E5:E12 as the red color code is 3.

• Press ENTER to have the output.

Thus, we can simply count the cells if the red color has been applied.

Read More: Excel Conditional Formatting Text Color (3 Easy Ways)

2. Calculate Summation When Cell Color Is Red

We can also calculate the summation of the specialized cells that are marked red. In that case, we can apply the SUMIF function. But firstly, we have to follow the same procedure.

Steps:

• First of all, find the Color Code using the same method mentioned in the previous section.

• Now, apply the formula mentioned below to have the summation of the salary in red cells.

Here, the SUMIF function looks through the range E5 to E12 whether any value matches with 3 or not. If they get matched, the connected values in the range D5:D12 are added.

• Finally, press ENTER to have the Total Salary in Red Cells.

Read More: How to Sum in Excel If the Cell Color Is Red (4 Easy Methods)

3. Using IF Function for Red Color Cell

The IF function can also be used in the red color cells to apply any specific function. For more clarification, I have considered a salary reduction of 25% for the salary connected with red color cells.

Steps:

• First of all, create a new column to have the updated salary considering the salary reduction for red cells.
• Now, apply the following formula in the Updated Salary column.

Here, I have mentioned the Identify_Red as Define Name. The IF function checks whether the defined name matches the red color code or not. Then, the salary reduction is applied and the salary gets updated.

• Press ENTER to have the updated salary.

Now, AutoFill the rest cells.

Read More: Excel Conditional Formatting Formula with IF

Similar Readings

• Conditional Formatting on Multiple Rows Independently in Excel
• How to Change a Row Color Based on a Text Value in a Cell in Excel
• Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)
• How to Use Conditional Formatting Based on VLOOKUP in Excel
• Excel Conditional Formatting for Dates within 30 Days (3 Examples)

4. Utilizing Filter and SUBTOTAL Function on Cells of Red Color

In terms of separating the red cells, we can use the Filter feature. After that, we can apply any functions according to our needs. Here, I have used the SUBTOTAL function.

Steps:

• Firstly, select the entire dataset.
• Next, go to the Home tab.
• Select Editing from the ribbon and choose Sort & Filter.
• Then, pick the Filter option.

• After that, click on the button in the title section.
• Then, Choose the red color from the Filter by Color option.

This is how we can filter the red cells.

• Now, apply the following formula to have the Total Salary in Red Cells.

Here, the SUBTOTAL function considers the Sum operation for the visible rows within D5:D12 cells by 109 number.

• Finally, hit ENTER to have our desired result.

Read More: Excel Formula to Color a Cell If the Value Follows a Condition

5. Applying VBA to Find Summation of Red Color Cells

Visual Basic for Applications (VBA) is the smartest way to work in Excel. We can also apply VBA to find the summation of red color cells.

Steps:

• Go to the Developer tab first.
• Next, click on Visual Basic from the ribbon.

Alternatively, press ALT + F11 to perform the same thing.

• Afterward, select the Insert tab.
• Click on Module.

• Now, write the following Code.

Function Red_Cells_Summation (p As Range, q As Range)
Dim m As Long
Dim n As Integer
n = p.Interior.ColorIndex
For Each i In q
If i.Interior.ColorIndex = n Then
m = WorksheetFunction.Sum(i, m)
End If
Next i
Red_Cells_Summation = m
End Function

Here, I have considered Red_Cells_Summation as Sub_procedure. I also used the ColorIndex property to consider the cell color and WorksheetFunction.Sum to have the summation value.

• Now, come back to the worksheet and create the Color and Total Salary in Red Cells section.
• Input Red color in the Color section.
• Along with that, apply the following formula in the Total Salary in Red Cells section.

Here, Red_Cells_Summation is a function that I mentioned in my VBA code. I have applied red color in cell C14 and applied the function in cell D5:D12.

• Press the ENTER button to have the summation value of red cells.

Read More: VBA Conditional Formatting Based on Another Cell Value in Excel

Practice Section

You can practice here for more expertise.

Conclusion

That’s all for today. I have tried my best to describe 5 practical scenarios of how to execute Excel functions if the cell color is red. It will be a great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.

Related Articles

• Excel Conditional Formatting Based on Multiple Values of Another Cell
• Conditional Formatting Based On Another Cell in Excel (6 Methods)
• How to Compare Two Columns Using Conditional Formatting in Excel
• Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
• 4 Quick Excel Formula to Change Cell Color Based on Date
• How to Make Negative Numbers Red in Excel (4 Easy Ways)