VBA Macro which counts fill colours automatic

February 17, 2011 at 07:13:14
Specs: Windows XP
Hey Guys,
I will be really impressed if any one can solve this because so far all attempts have been futile!

I have created a VBA macro code on my Excel spreadsheet that enables me to count the number of green fill boxes.
The problem is, in order to calculate the number, i have to manually click on the cell/code and press enter.
Can anybody find a way of changing the code or otherwise that will mean the cell automatically updates when i add a new green fill box?!?!

Here is my code
Function ColorFunction(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


ColorFunction = vResult

End Function


See More: VBA Macro which counts fill colours automatic

Report •

#1
February 17, 2011 at 10:22:18
As far as I can tell, you have 2 issues:

1 - User Defined Functions (UDF) don't recalculate automatically unless you inlcude the line:

Application.Volatile

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
  Application.Volatile
   lCol = rColor.Interior.ColorIndex
 etc.
 etc.
 ...
End Function


2 - Even if you include that line, changing a cell color does not cause the sheet to recalculate like changing a value would.

So, I think you need to do 2 things:

1 - Add that line to your code.
2 - Force a calculation

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

You can refine that by limiting the range in which a mere selection forces a recalculation, but I think you get the idea.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 18, 2011 at 01:46:59
Hey DerbyDad,
Thank you very much for your excellent response!
To be honest, i am pretrty rubbish at this because i keep getting errors.

Any chance you chould how me how to put the application.volatile code and the force a calculation in to my code?!

Function ColorFunction(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


ColorFunction = vResult

End Function

Thanks! you are a hero!


Report •

#3
February 18, 2011 at 04:05:08
Please re-read my post earlier.

I already showed you where the Application.Volatile line goes.

I also included the SelectionChange code which will force a calculation whenever you select a cell. Right click the sheet tab for the sheet in which you want this to occur and choose View Code. Paste the SelectionChange code into the pane that opens.

2 notes:

1 - I can't see your worksheet from where I'm sitting, so telling me that you are getting errors doesn't accomplish much. You need to tell me what the errors are and on what line they occur.

2 - Before you post any more code, please read the How To referenced in my signature line. It will explain how to post VBA code so that it retains it's indentation and is easier to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 18, 2011 at 04:17:24
Hey

It worked!
Thank you very much, seriously no body has been able to tell me how to do this!

Sorry for not reading properly, i don't really understand where the sub heading etc go without mucking it up and i didn't know about the view code on sheet thing..

I have a lot to learn!

Many thanks,
Sam


Report •

Ask Question