Andreas Hermle
asked on
Count by Color using VBA
Dear Expert:
Entering the following formula into the formula bar, the number of cells in Column E that contain the color in E2 are counted
countbycolor(E2,E1:E15)
The user-defined worksheet function works fine but I would like to get the whole thing automated, i.e. ...
A macro is ...
... to write this formula into B7 of the worksheet named 'Results' of the active workbook
... and the counting of the shaded cells is to be effected in Column E of a sheet named 'Evaluation' and where E5 is the cell reference that contains the background color (to be counted).
... The range of cells can be any number, e.g. E1:465 or E1:3244, i.e the range is variable
It would be great if somebody could help me with that. Help is much appreciated. Thank you very much in advance.
Regards, Andreas
Entering the following formula into the formula bar, the number of cells in Column E that contain the color in E2 are counted
countbycolor(E2,E1:E15)
The user-defined worksheet function works fine but I would like to get the whole thing automated, i.e. ...
A macro is ...
... to write this formula into B7 of the worksheet named 'Results' of the active workbook
... and the counting of the shaded cells is to be effected in Column E of a sheet named 'Evaluation' and where E5 is the cell reference that contains the background color (to be counted).
... The range of cells can be any number, e.g. E1:465 or E1:3244, i.e the range is variable
It would be great if somebody could help me with that. Help is much appreciated. Thank you very much in advance.
Regards, Andreas
Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next TCell
End Function
ASKER
Hi MacroShadow,
thank you very much for your great and swift support.
I am afraid to tell you that the code is throwing an error code on line 15: Error Code 91.
I am doing some more testing.
Again, thank you very much. Regards, Andreas
thank you very much for your great and swift support.
I am afraid to tell you that the code is throwing an error code on line 15: Error Code 91.
I am doing some more testing.
Again, thank you very much. Regards, Andreas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, macro shadow, great. This did the trick. Thank you very much for your great and professional support. I really appreciate it. Regards, Andreas
Glad to help:)
Open in new window