locked
Why the public function is not updated RRS feed

  • Question

  • Hello

    I use the following function to count specific cells
    But this function does not update itself
    Should I click on the cell every time to update?

    Public Function CouFColor(rData As Range, cellRefColor As Range, min As Long, max As Long) As Long

        Dim indRefColor As Long
        Dim cellCurrent As Range
        Dim cntRes As Long
        
       Application.Volatile
      cntRes = 0
        indRefColor = cellRefColor.Cells(1, 1).Font.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Font.Color And _
                cellCurrent.Value >= min And cellCurrent.Value <= max Then
                cntRes = cntRes + 1
            End If
        Next cellCurrent
        
        CouFColor = cntRes
        
    End Function

    Regard
    Tuesday, January 2, 2018 8:32 PM

Answers

  • If you have set calculation options to automatic, a formula using this function should be updated automatically when you change the value of cells in the rData range.

    But if you change the text color of cells, the formula will not be recalculated until you select the cell and press F9 or click Calculate Now on the Formulas tab of the ribbon. This is because cell formulas react to changes in the value of cells, not to changes in formatting.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Wednesday, January 3, 2018 6:26 AM
    • Marked as answer by Kaprio Wednesday, January 3, 2018 9:30 AM
    Tuesday, January 2, 2018 9:01 PM

All replies

  • If you have set calculation options to automatic, a formula using this function should be updated automatically when you change the value of cells in the rData range.

    But if you change the text color of cells, the formula will not be recalculated until you select the cell and press F9 or click Calculate Now on the Formulas tab of the ribbon. This is because cell formulas react to changes in the value of cells, not to changes in formatting.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Wednesday, January 3, 2018 6:26 AM
    • Marked as answer by Kaprio Wednesday, January 3, 2018 9:30 AM
    Tuesday, January 2, 2018 9:01 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, 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


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, January 3, 2018 2:12 AM
  • thanks
    Wednesday, January 3, 2018 9:31 AM