Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Ssumming colored values using UDF

    I have the folowing code to set up coloured values, but when I use the formula to sum the coloured values in col I , using
    =sumcolour(I1,I10:I298) , I get zero

    The coloured values are generated per conditional formatting

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    This code works. That is if the color in I1 = the Color in the highlighted rows which in your sample it did not! I pasted the format from I10 into I1 and then it worked. Although I tried to test the answer by selecting the cells and checking the status bar and it was different. This tells me that not all the highlights in the range are the same color.
    Code:
    Option Explicit
    
    Public Function SumColour(rngSumColor As Range, rngSumRange As Range) As Long
    
       Dim MyCell            As Range
       Dim ColourIndexNumber As Integer
    
       SumColour = 0
       ColourIndexNumber = rngSumColor.Interior.ColorIndex
    
       For Each MyCell In rngSumRange
          If MyCell.Interior.ColorIndex = ColourIndexNumber Then
            SumColour = SumColour + MyCell.Value
          End If
       Next MyCell
    
    End Function
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the colours are from conditional formatting, then the function won't work since the Interior.Colorindex is unaffected by CF. I strongly suggest you don't try using colours as data - use an additional column for the calculation you require and then use that column for the summing and for applying the CF.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks I didn't know that.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Rory & Retired Geek

    Thanks for the assistance, much appreciated

    Regards

    Howard

Posting Permissions

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