Formula function by date

[minas84]

Hello all, I need some help. In this test excel I have a formula to read odds color(Green and Red)
Function CheckColor(range)
If range.Interior.Color = RGB(256, 0, 0) Then
CheckColor = "Red"
ElseIf range.Interior.Color = RGB(0, 176, 80) Then
CheckColor = "Green"
Else
CheckColor = "Neither"
End If
End Function




Also a module that
-6 for each red
+(cell value-1)*0.97 for each green

Function Daily(MyRange)
Daily = 0
For Each Cell In MyRange
If Cell.Interior.Color = RGB(255, 0, 0) Then
Daily = Daily - 6
ElseIf Cell.Interior.Color = RGB(0, 176, 80) Then
Daily = Daily + (Cell.Value - 1) * 0.97
Else: Daily = Daily
End If
Next Cell
End Function





I also find function to read the quantity of each day
24 Feb is 3
25 Feb is 1
etc

Now I want some more difficult function.

For the 3 odds of 24 Feb (3.58 red , 1.58 red , 2.77 green)
starting from 0

-6 for each red

(cell value-1)*0.97 for each green

So I want 0 -6 -6 +(2.77-1)*0.97 = -10.28


For 1 odds of 25 Feb (15.12 red)
starting from 0
0-6 = -6

For 4 odds of 26 Feb (7.00 green , 1.80 red , 3.56 green , 2.78 green
starting from 0
0 +(7.00-1)*0.97 -6 +(3.56-1)* 0.97 +(2.78-1)*0.97 = 4.03

etc




What am I doing wrong in this one?

I need to get results from "Daily" module on F1:F100 cells, if A1:A100 has specific "24 Feb"

 

[minas84]

Could you help me? Thank you in advance!

 

[minas84]

Any ideas?