# Can I use the background color of an Excel cell as a condition in a formula?

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?

• Short answer: yes. – DavidPostill Jan 22 '18 at 0:38
• What defines the background color (what makes some cells red, some green)? – fixer1234 Jan 22 '18 at 0:53
• Don't you want to ask how to do this?? – Twisty Impersonator Jan 22 '18 at 3:57

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.