Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Colored Cells & Cell Formulas

  1. #11
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    These are all good options.
    Also search the board, this has been covered
    with lots of examples and site references.
    Kind Regards,
    Ivan F Moala From the City of Sails

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Will the UDF Colorsumif work for cells that are colored using conditional formatting? I've seen a few posts like this and they all give answers but warns that it doesn't work for conditionally formatted cell. Why is this the case?

  3. #13
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Conditional formatting doesn't operate the same, to put it mildly. I did post a couple of solutions (leveraging off of others, credit was posted respectively), but they only work on small ranges. You're better off testing the data for the same criteria that the was used to apply the format. To the matter at hand. Here's a udf to test, haven't tried Nimrod's, but if you want the recalc. to work properly, you need to mention that the function is volatile (vba style):

    Code:
    Function SumByColor(InputRange As Range, ColorRange As Range) As Double
    Dim cl As Range, TempSum As Double, ColorIndex As Integer
    Application.Volatile
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Interior.ColorIndex = ColorIndex _
    Then TempSum = TempSum + cl.Value
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
    End Function
    Now use =sumbycolor(B1:F18,D1) where the first parameter is the test range and the second param is a cell that holds the format. This is leveraged from http://www.erlandsendata.no/english/...sumbycolor.htm, but I've elected to add the volatility.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-11 11:51 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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