1

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?

  • 2
    Short answer: yes. – DavidPostill Jan 22 '18 at 0:38
  • 1
    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
1

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.

0

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().

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.