As I say in the title, can I use the background color or the format of an Excel cell as a condition in a formula?

For example: I have a grid of numbers and names that I select by hand if I want the cell to be in a "red" format or "green" format. And I would like to have a counter of how many "green" and "red" cells I have.

Is it possible? Can you think another way to achieve something similar?

3

3 Answers

You need to create a named reference with any name with reference like:

"GET.CELL(38," is get background color with number value.

Then put that value in another cell with:

=CellColor

Although CellColor is your given name that any name you given. In my case I put this in B17 cell.

Then you can check condition with like formula below. I my case I put it to D17.

=IF(B17=2,0,C17)

It means if A17 is light grey background then get value to D17 is 0 or get value of C17.

---

I just found this from this great explanation video:

It is little bit complicated. But it is really works.

2

To answer your question as posted: NO.

If you manually formatted the cell fill colour, then you will need VBA to evaluate the cell fill as a input for a formula. That will require programming, not just writing an Excel cell formula.

If you use conditional formatting to set a cell fill colour, then YES, you can use the same logic as in the conditional formatting rule to further process cells for summing or counting. Look into Sumifs() and Countifs().

Yes, you can use Cell Background Colours in the Formula.

Now, I'm picking next one "How to Format Cells using Formula".

Since you have not written and specific conditions to Format in RED & GREEN so that I would like to show you how to Fill cell or Range of Cells with colour, based on their Data or based on other Cells value.

=OR(A2="NY",A2="Moscow",A2="New Delhi")
=AND(A1>1000,B1<500)
=AND(A2>NOW(),A2<=(NOW()+20))
=If(and(A2>=100, A2<=500))

NB: In above examples I've used Column A. These Values can be tested to Format other Columns but, in that case you need to select Both or All Columns first then apply the Formula.

Now, I would like to solve the other part that "how to set the Colour Counter".

You can Count the Cells on the basis of their Background Colour by using below written VBA Code.

 Function CountByColour(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell
Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult End If Next rCell End If CountByColour = vResult End Function

NB: Copy & Paste this code as MODULE using VBA Editor and, write Formula like this to use the Function.

=CountByColour(A2,B2:J6,FALSE), where A2 is Sample Cell and B2:J6 is the Range where Colour like A2 is to be Counted.

Note, This code was tested by me before I've posted it here.

Hope this help you.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy