vba color definition

homeric

New Member
Joined
Dec 3, 2011
Messages
32
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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 :confused:
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Thanks very much FireFly2012
Your modified code does just what it says on the can!
Regards homeric :)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top