Page 1 of 2 12 LastLast
Results 1 to 10 of 11

vba color definition

This is a discussion on vba color definition within the Excel Questions forums, part of the Question Forums category; This vba works fine & I can edit it to find cells with a blue font, but when I want ...

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    26

    Default vba color definition

    This vba works fine & I can edit it to find cells with a blue font, but when I want it to find green, it defaults to finding Bright Green (I need it to find Dark Green, but don't know how to enter this in place of the red references below) - Can anyone put me straight on this please?

    Code:
     
    FindRed
    Public Function FindRed(rngRed As Range) As Long
        Application.Volatile
        Dim c As Range
        For Each c In rngRed
            If c.Font.Color = vbRed Then Let FindRed = FindRed + 1
        Next c
    End Function

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: vba color definition

    Hi

    Probably best to include a reference cell in the function arguments (said reference cell would hold the color properties you wish to search on) eg:

    Code:
    Public Function FindColor(rng As Range, refCell As Range) As Long
        Application.Volatile
        Dim c As Range
        For Each c In rng
            If c.Font.Color = refCell.Font.Color Then Let FindColor = FindColor + 1
        Next c
    End Function
    Then use in your worksheet like:

    =FindColor(A1:C100,D1)

    assuming D1 was formatted to the same color that you wish to find.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    New Member
    Join Date
    Dec 2011
    Posts
    26

    Default Re: vba color definition

    Thanks Firefly2012 your reference cell idea has worked exactly as I need - homeric

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    751

    Default Re: vba color definition

    You can also use RGB values to set colors.

    Insert code here = rgb(#, #, #)

  5. #5
    New Member
    Join Date
    Dec 2011
    Posts
    26

    Default Re: vba color definition

    Thanks BrianExcel that'd also do what I need, however I've now moved on a stage and have again got bogged down - Using FindColor in this way:- =FindColor(A1:C100,D1)
    is fine for giving me a count of how many cells have the same color as the reference cell, (eg 9), but now I'd like to have a Sum of the contents of those cells

    I tried =Sum(FindColor(A1:C100,D1) but I still got the result of 9 - I've tried several variants but they either don't work or give me the same 9

    Is it not possible to sum the colored font cells or do I need an entirely different approach - I'm confused

  6. #6
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: vba color definition

    You would need to modify the function code - consider:

    Code:
    Public Function FindColor(rng As Range, refCell As Range, Optional blnSum As Boolean) As Variant
        Application.Volatile
        Dim c As Range
        Dim temp As Double
        Dim cnt As Long
        For Each c In rng
            If c.Font.Color = refCell.Font.Color Then
                cnt = cnt + 1
                temp = temp + Iif(Isnumeric(c),c,0)
            End If
        Next c
        If blnSum Then FindColor = temp Else FindColor = cnt
    End Function
    This introduces an optional third boolean parameter whereby if you leave it out (or set it to FALSE or 0) it will return the count, but set it to True and it will return the Sum eg:

    =FindColor(A1:A10,D1,True)

    Will return the sum of A1:A10 where they match color of D1, but use:

    =FindColor(A1:A10,D1,False)

    and it will return the count of matching cells nistead
    Last edited by Firefly2012; Mar 3rd, 2012 at 07:08 AM.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  7. #7
    New Member
    Join Date
    Dec 2011
    Posts
    26

    Default Re: vba color definition

    Sorry previous post should've said
    ...I tried =Sum(FindColor(A1:C100,D1)) but I still got the result of 9...
    didn't type the last closing paranthesis

  8. #8
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: vba color definition

    I have edited my post above to change the return type of the function
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  9. #9
    New Member
    Join Date
    Dec 2011
    Posts
    26

    Default Re: vba color definition

    Thanks very much FireFly2012
    Your modified code does just what it says on the can!
    Regards homeric

  10. #10
    shg
    shg is online now
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,396

    Default Re: vba color definition

    Just a comment: If blnSum = True, the function will add numeric values stored as text. If you want only numbers summed,

    Code:
    temp = temp + IIf(VarType(c.Value2) = vbDouble, c.Value2, 0)
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

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
  •  


DMCA.com