Macro help.

[KurtisT6]

This is My macro:

I want to count each cell depending on the colour for a particular range. Below is the example of the macro used:

The bracket in red is the range I am searching for the colour specified in cell B1.

This "CountByResult" only works when I click on the cell that contains the macro formula above and press enter.

What I want to happen is for the cell to update automatically as I am changing the colours of a lot of cells and have about 36 colomns with similar formulas using "CountByResult". Therefore, I want it to be fast and not tedious to keep clicking cells and pressing enter.

 

[DanteAmor]

Check if the following helps you.

1. Add this line to your function:

Application.Volatile

2. Recommendation:

Do not use variables with the same name as reserved words, you may have errors or not work properly, for example "count":​

Public Function countbyresult(cellrange As Range, targetcell As Range)
  Dim targetcolor As Long, c As Range
  Dim nCount As Long
 
  Application.Volatile
  targetcolor = targetcell.Interior.ColorIndex
  For Each c In cellrange
    If c.Interior.ColorIndex = targetcolor Then nCount = nCount + 1
  Next c
  countbyresult = nCount
End Function

3. Add the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.Calculate
End Sub

Note Sheet Event

Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.​

​

With the above. you change the color of the cell and selecting any cell will update the calculations.​

4. Note Code Tag:

In future please use code tags when posting code. Instead of an image.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

--------------
Cordially
Dante Amor
--------------​