*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.*

howto do this?? – Twisty Impersonator Jan 22 '18 at 3:57