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.

Excel If Cell Color is Red then


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.

Counting Red Color Cells

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.
=GET.CELL(63,COUNT!B15)

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
=Identify_Red

Here, I have mentioned the defined name.

  • Press ENTER to have the color code.

Counting Red Color Cells

  • Now, input the following formula to have the Number of Red Cells.
=COUNTIFS(E5:E12,3)

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

Counting Red Color Cells

  • 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:

Calculate Summation When Cell Color Is Red

  • Now, apply the formula mentioned below to have the summation of the salary in red cells.
=SUMIF(E5:E12,3,D5:D12)

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.

 

Using IF Function in 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.
=IF(Identify_Red=3, D5*(1-$C$14),D5)

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


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.

Utilizing Filter and SUBTOTAL Function on Red Cells

  • 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.
=SUBTOTAL(109,D5:D12)

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.

Utilizing Filter and SUBTOTAL Function on Red Cells

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.

Applying VBA to Find Summation of Red Color Cells

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

Applying VBA to Find Summation of Red Color Cells

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.
=Red_Cells_Summation(C14,$D$5:$D$12)

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.

Applying VBA to Find Summation of Red Color Cells

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


Practice Section

You can practice here for more expertise.

For Pracrice


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo