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.
If Cell Color Is Red Then Execute Different Functions in Excel: 5 Operations
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.
- Define Name
- Applying the COUNTIFS Function
Steps:
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_RedHere, 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.
=COUNTIFS(E5:E12,3)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.
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.
=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.
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.
=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.
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.
=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.
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 code 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.
=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.
Practice Section
You can practice here for more expertise.
Download Practice Workbook
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 would be a great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.
