none
Updating VB Module in Excel RRS feed

  • Question

  • Hello, I obtained the following code to count how many cells within a range are highlighted in a certain color.  The problem is Excel does not update the value in the cells.  I found if I just double click on the individual cells, they will update.  Unfortunately, there are several cells.  Is there a way I can get these cells to run the VB Module automatically (In other words, when I change the highlight of a particular cell in the range, the "COUNT" cell should automatically change its value)?  Please keep in mind, I am not a "Coder", so please dumb it down for me.

    Thank you in advance for any and all help!

    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

     

    Tuesday, October 11, 2016 7:19 PM

Answers

  • Excel calculates formulas if some values changes in cells. It may be based on input cells or any other cell. But it will not calculate if formatting changes. SUBTOTAL is only one exception which calculates on filtering/hiding rows.

    In your case only option is force recalculation by pressing F2 in formula cells. (Not input cells)

    Say your formuls in G10  is   =ColorFunction(D3,D2:D10,TRUE). And you have copies till G21. You can press F2 from G10 till G21.

    But if number of formula cells is more do below..

    Select from G10:G21 and go Data Tab->Click "Text to Columns"-> Click Finish.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 5:07 AM
    Answerer

All replies

  • Excel calculates formulas if some values changes in cells. It may be based on input cells or any other cell. But it will not calculate if formatting changes. SUBTOTAL is only one exception which calculates on filtering/hiding rows.

    In your case only option is force recalculation by pressing F2 in formula cells. (Not input cells)

    Say your formuls in G10  is   =ColorFunction(D3,D2:D10,TRUE). And you have copies till G21. You can press F2 from G10 till G21.

    But if number of formula cells is more do below..

    Select from G10:G21 and go Data Tab->Click "Text to Columns"-> Click Finish.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 5:07 AM
    Answerer
  • Hi,

    Please refer to the reply of Asadulla Javed and confirm if it will work for you.

    You problem is related to code in Excel, I'll move your question to the MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, October 12, 2016 9:07 AM