Hi all,
I want to know how much cell have a blue Font color within the cells that have a green background (and so on, 5 background colors and 5 Font colors).
Exemple: I have 20 cells that have a green background, from those cells how much have a blue Font?
Here are my formulas:
Background color count:
Function ColorCount(SearchRange As Range, colorRange As Range) As Long
Dim cell As Range, a As Range, b As Range, n As Integer
' preload for Union method (will Union with itself in first For loop)
Set b = SearchRange(1).MergeArea(1)
For Each cell In SearchRange
Set a = cell.MergeArea(1)
Set b = Union(a, b)
Next
' a becomes the preload for the next Union; n will be used to exclude
' it from the count if it's not the right color
n = a.Interior.Color = colorRange.Interior.Color
For Each cell In b
If cell.Interior.Color = colorRange.Interior.Color Then
Set a = Union(cell, a)
End If
Next
ColorCount = a.Count - 1 - n
End Function
-------------------------------------
*Please note that I need to use some merged cells in my file (no way out of it) and I need them to count as one cell. This Function seems to work perfectly for that.
Font Color count:
Function FontColorCount(SearchRange As Range, colorRange As Range) As Long
Dim cell As Range, a As Range, b As Range, n As Integer
' preload for Union method (will Union with itself in first For loop)
Set b = SearchRange(1).MergeArea(1)
For Each cell In SearchRange
Set a = cell.MergeArea(1)
Set b = Union(a, b)
Next
' a becomes the preload for the next Union; n will be used to exclude
' it from the count if it's not the right color
n = a.Font.Color = colorRange.Font.Color
For Each cell In b
If cell.Font.Color = colorRange.Font.Color Then
Set a = Union(cell, a)
End If
Next
FontColorCount = a.Count - 1 - n
End Function
---------------------------
I am stuck there! Help! I don't know how to use a function within an other one. Also would I need to use a Find function instead of a count Function ie: =Count Font color = Reference cell, IF(Background color = Reference cell) (Rough idea of what I want to do).
Thank you all for your support!
I want to know how much cell have a blue Font color within the cells that have a green background (and so on, 5 background colors and 5 Font colors).
Exemple: I have 20 cells that have a green background, from those cells how much have a blue Font?
Here are my formulas:
Background color count:
Function ColorCount(SearchRange As Range, colorRange As Range) As Long
Dim cell As Range, a As Range, b As Range, n As Integer
' preload for Union method (will Union with itself in first For loop)
Set b = SearchRange(1).MergeArea(1)
For Each cell In SearchRange
Set a = cell.MergeArea(1)
Set b = Union(a, b)
Next
' a becomes the preload for the next Union; n will be used to exclude
' it from the count if it's not the right color
n = a.Interior.Color = colorRange.Interior.Color
For Each cell In b
If cell.Interior.Color = colorRange.Interior.Color Then
Set a = Union(cell, a)
End If
Next
ColorCount = a.Count - 1 - n
End Function
-------------------------------------
*Please note that I need to use some merged cells in my file (no way out of it) and I need them to count as one cell. This Function seems to work perfectly for that.
Font Color count:
Function FontColorCount(SearchRange As Range, colorRange As Range) As Long
Dim cell As Range, a As Range, b As Range, n As Integer
' preload for Union method (will Union with itself in first For loop)
Set b = SearchRange(1).MergeArea(1)
For Each cell In SearchRange
Set a = cell.MergeArea(1)
Set b = Union(a, b)
Next
' a becomes the preload for the next Union; n will be used to exclude
' it from the count if it's not the right color
n = a.Font.Color = colorRange.Font.Color
For Each cell In b
If cell.Font.Color = colorRange.Font.Color Then
Set a = Union(cell, a)
End If
Next
FontColorCount = a.Count - 1 - n
End Function
---------------------------
I am stuck there! Help! I don't know how to use a function within an other one. Also would I need to use a Find function instead of a count Function ie: =Count Font color = Reference cell, IF(Background color = Reference cell) (Rough idea of what I want to do).
Thank you all for your support!
