macro that counts lines with certain color

  • Thread starter Philipp Oberleitner
  • Start date
P

Philipp Oberleitner

hi all i need a macro that counts the lines with ColorIndex = 3 and gives it
out in a msg box. It should do the same for colorindex 4 and 5.

Thx alot in advance
 
P

Philipp Oberleitner

Mine doesnt work but i dont know why

Sub StatistikAusgeben()
Const Stat1 = "Open Tickets"
Dim i As Integer
Dim iAnz As Integer
Sheets(Stat1).Activate
Range("A1").Select

iAnz = 0
i = 0

Do Until i = ActiveSheet.UsedRange.Rows.Count
If Selection.Interior.Color = RGB(128, 255, 196) Then
iAnz = iAnz + 1
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
i = i + 1
Loop

MsgBox "Es wurden " & iAnz & " kritische Tickets gefunden"
End Sub


Thx alot
 
T

Tom Ogilvy

using color is probably the source of your problem. Excel only used 56
colors for cells, so good chance your color is not one of them. Better to
use colorindex

Sub StatistikAusgeben()
Const Stat1 = "Open Tickets"
Dim i As Integer
Dim iAnz As Integer
Sheets(Stat1).Activate
Range("A1").Select

iAnz = 0
i = 0

Do Until i = ActiveSheet.UsedRange.Rows.Count
If Selection.Interior.ColorIndex = 3 Then
iAnz = iAnz + 1
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

MsgBox "Es wurden " & iAnz & " kritische Tickets gefunden"
End Sub
 
Top