+ Reply to Thread
Results 1 to 2 of 2

"Refresh" custom function in cell with a macro

Hybrid View

  1. #1
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    "Refresh" custom function in cell with a macro

    I am using the following functions together to get an average of colored cells.
    Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    '(Range,Cell of Color)
        Dim indRefColor As Long
        Dim cellCurrent As Range
        Dim cntRes As Long
     Application.Volatile True
        cntRes = 0
        indRefColor = cellRefColor.Cells(1, 1).Interior.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Interior.Color Then
                cntRes = cntRes + 1
            End If
        Next cellCurrent
     
        CountCellsByColor = cntRes
        Application.Volatile False
    End Function
     
    Function SumCellsByColor(rData As Range, cellRefColor As Range)
    '(Range,Cell of Color)
        Dim indRefColor As Long
        Dim cellCurrent As Range
        Dim sumRes
     Application.Volatile True
        sumRes = 0
        indRefColor = cellRefColor.Cells(1, 1).Interior.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Interior.Color Then
                sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
            End If
        Next cellCurrent
     
        SumCellsByColor = sumRes
        Application.Volatile False
    End Function
    I am using:
    Sub ColorBlue()
        Application.DisplayAlerts = False
        Selection.Interior.Color = 250 
        Range("A14:M14").CalculateRowMajorOrder
        Application.DisplayAlerts = False
    End Sub
    to change the cell color to the desired color and I would like it to "refresh" the formulas in Range(A14:M14). I have tried Calculate and CalculateRowMajorOrder. CalculateRowMajorOrder changed the values the first time but when I tried on a different cell it didn't update again. Thoughts on how to make this work?

  2. #2
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: "Refresh" custom function in cell with a macro

    I was able to get it figured out.
    Sub ColorBlue()
        Application.DisplayAlerts = False
        Selection.Interior.Color = 250 
        Range("A14:M14").Replace What:="=", Replacement:="="
        Range("A14:M14").Calculate
        Application.DisplayAlerts = False
    End Sub


    It replaces all the '=' in the activesheet with '=' thereby forcing a recalculation.

    Thank you to all who looked

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2017, 11:45 PM
  2. [SOLVED] Excel 2010 -- "Visual Basic" "Macros" and "Record Macro" all disabled.
    By NicholasL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2017, 06:11 AM
  3. [SOLVED] Convert from "general" (YYYYMMDD) to specific "custom" format (YYYY.MM.DD)
    By Ella_p in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2013, 02:23 AM
  4. Macro to "refresh data" in a excel linked chart in powerpoint during the slideshow
    By Gordon Wan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2012, 01:44 PM
  5. Replies: 2
    Last Post: 11-01-2012, 04:35 PM
  6. Macro won't wait "Refresh all Pivot Tables" command to conclude before calculating
    By malalol in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2010, 04:31 PM
  7. [SOLVED] Macro does not refresh on the "Calculate" command.
    By ch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2005, 03:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1