This will check the cells in a range for the value of 3 and if found will
check if the interior.colorindex = 35 and if so, it will display a message
for the total cells found that meet the criteria.


Sub countColNdx()
Dim rng As Range, sh As Worksheet, c As Range
Dim x As Long
Set sh = ActiveSheet
Set rng = sh.Range("F35:F285")
For Each c In rng
If c.Value = 3 And c.Interior.ColorIndex = 35 Then
x = x + 1
End If
Next
MsgBox "There are " & x & " cells with color index 35" _
& " and a value of 3"
End Sub




"mns" wrote in message
...
Hi there,
I am using a macro to count colored cells. You can see the code below.
Then I use to count the cells with
=SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35))
where 35 is the colorindex (i got it with =colorindex(a1) )
now i need to and an if statement:)
something like,
scan the range of F33:F285, find the cells containing number 3,
check if these cells has colorindex 35.

with a noob approch it'd be like
=IF(COUNTIFS(Sheet1!B33:B285,3),SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35)))
I thing I cannot figure out if/then structure right:(

any ideas?

thanx in advance.


Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant

Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = _
DecodeColorIndex(cell, True, iBlack)
Else
aryColours(i, j) = _
DecodeColorIndex(cell, False, iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, _
text As Boolean, _
idx As Long)
Dim iColor As Long
If text Then
iColor = rng.Font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function