gibt es eine Funktion, um die Anzahl der Zellen in einen Bereich zu bestimmen, die bspw. einen grauen Hintergrund besitzen?
Rolf
A | B | C | D | E | F | |
1 | 1 | Summe | 14 | |||
2 | 2 | Summe | 17 | |||
3 | 5 | Summe | 10 | |||
4 | 4 | |||||
5 | 9 | |||||
6 | 8 | |||||
7 | 3 | |||||
8 | 9 | |||||
9 |
Formeln der Tabelle | ||||||||
|
A | B | C | D | E | F | G | |
1 | 1 | Summe | 48 | 48 | |||
2 | 2 | Summe | 56 | 56 | |||
3 | 3 | Summe | 42 | 42 | |||
4 | 4 | Summe | 64 | 64 | |||
5 | 5 | ||||||
6 | 6 | ||||||
7 | 7 | ||||||
8 | 8 | ||||||
9 | 9 | ||||||
10 | 10 | ||||||
11 | 11 | ||||||
12 | 12 | ||||||
13 | 13 | ||||||
14 | 14 | ||||||
15 | 15 | ||||||
16 | 16 | ||||||
17 | 17 | ||||||
18 | 18 | ||||||
19 | 19 | ||||||
20 | 20 |
Formeln der Tabelle | ||||||
|
Public Function CellColor(Target As Range, RefColor As Variant, Optional FontColor As Boolean = False) As Variant
Dim vntRet As Variant
Dim lngColor As Long, lngRow As Long, lngCol As Long
Application.Volatile
If TypeName(RefColor) = "Range" Then
If Not FontColor Then
lngColor = RefColor.Interior.Color
Else
lngColor = RefColor.Font.Color
End If
Else
lngColor = RefColor
End If
Redim vntRet(1 To Target.Rows.Count, 1 To Target.Columns.Count)
For lngRow = 1 To Target.Rows.Count
For lngCol = 1 To Target.Columns.Count
If Not FontColor Then
vntRet(lngRow, lngCol) = Target(lngRow, lngCol).Interior.Color = lngColor
Else
vntRet(lngRow, lngCol) = Target(lngRow, lngCol).Font.Color = lngColor
End If
Next
Next
CellColor = vntRet
End Function
Public Function CellColor2(Target As Range, Optional FontColor As Boolean = False) As Variant
Dim vntRet As Variant
Dim lngColor As Long, lngRow As Long, lngCol As Long
Application.Volatile
Redim vntRet(1 To Target.Rows.Count, 1 To Target.Columns.Count)
For lngRow = 1 To Target.Rows.Count
For lngCol = 1 To Target.Columns.Count
If Not FontColor Then
vntRet(lngRow, lngCol) = Target(lngRow, lngCol).Interior.Color
Else
vntRet(lngRow, lngCol) = Target(lngRow, lngCol).Font.Color
End If
Next
Next
CellColor2 = vntRet
End Function