praktikant
New Member
- Joined
- Dec 9, 2014
- Messages
- 9
Hi guys,
I've been working on a spreadsheet for some time now and looked through several forums and threads to find an answer, but cant find any at all.
The sheet is more or less like a gantt-chart with activities down column A and duration/weeks along the rows
http://tinypic.com/r/xnfb6p/8
I've made it so the user only have to enter the start week and the duration (how many weeks) in the cells and conditional formatted will do the rest by coloring the duration orange.
As of now there are 154 activities with different start dates and durations, which is why I want a color count of activities on a weekly basis.
I tried:
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
and
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
When I use the countbycolor it will count colored cells, but NOT the conditional formatted which is the purpose of the entire thing.
http://tinypic.com/r/2rddxsy/8
Can any of you see what i'm doing wrong?
I've been working on a spreadsheet for some time now and looked through several forums and threads to find an answer, but cant find any at all.
The sheet is more or less like a gantt-chart with activities down column A and duration/weeks along the rows
http://tinypic.com/r/xnfb6p/8
I've made it so the user only have to enter the start week and the duration (how many weeks) in the cells and conditional formatted will do the rest by coloring the duration orange.
As of now there are 154 activities with different start dates and durations, which is why I want a color count of activities on a weekly basis.
I tried:
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
and
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
When I use the countbycolor it will count colored cells, but NOT the conditional formatted which is the purpose of the entire thing.
http://tinypic.com/r/2rddxsy/8
Can any of you see what i'm doing wrong?
Last edited:
