Hi
you can't invoke this macro with the macro dialog. It is a user defined
function which can be used like a normal Excel formula (e.g. I used it
within the SUMPRODUCT function). So don't change it from Function to
Sub :-)
Have a look at the website I posted to get some basicc information
about this kind of functions.
So in your Excel version use the following function call within a cell
=(PRODUKTSUMMA(--(ColorIndex(B1:O1)=3))*15)/24
--
Regards
Frank Kabel
Frankfurt, Germany
Jonsson wrote:
Hi Frank!
I´m really grateful!! I´m trying to learn as much as possible, thats
why I have so many questions.
I´m using the swedish version of excel.
"Drive"? I mean run of course!!
When I try to run the macro it "pops up" a dialogbox that ask for the
name of the macro. I´ve tried to change from Function to Sub(public)
but then I get errors in the macro when I try to run it.
I have discovered that I can format the cells as ##":"##.
That way I can write "2200" in the cell, and the result is 22:00. Do
you think that is a better solution?
However, I ran into a problem with a special formula when doing so
and cant understand why.
=SUM.IF('v2'!$O$3:$O$152;$C6;'v2'!$S$3:$S$152)
Any ideas?
//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
see below - so many questions :-)
Jonsson wrote:
Hi Frank!
First...
Is it impossible to have the time as "2200"? It would be easier to
write than to write "22:00"......................?
You can write is this way but all calculation formulas would get
quite complex as you loose Excel's time support - don't do this.
Add the ':' Though it is possible to write a worksheet_change macro
which will convert these entries to a time format i would stick to
the normal entry
(but if you're interested have a look at
http://www.cpearson.com/excel/DateTimeEntry.htm for such code)
Second.....
I assume I´m supposed to drive the code, but when I do, I get the
question "Makroname".
One question up-front: Do you use the English Excel version (your
word 'Makroname' suggests a non english version).
What do you mean with 'drive'?. Do the following:
- Open your workbook
- Hit ALT F11 to oben the VBA editor
- Create a new module (right click in the explorer tree and add a
new
module)
- paste the code
- close the VBA editor.
Third.......
When I paste the formula in AA1 I get "Name?"
Should be solved by the above (for some more information how to use
macros have a look at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm)
Fourth.......
How to change the code so that I get a separate value
if I make one cell in another color ?
In short terms this is what I want to do:
color1 from B1 to O1 is 16 cells, these cells are representing 15
minutes each, totally 4 hours.
That give me in B2"0800" and C2"1200"
B1 represent "0800" and O1 represent "1200".
so, depending of how many cells you colors you get a time value at
lets say Z1.
Also, there is a need of if I color any of these cells in Color2
the
total value of the cells Z1 should be decreased by that number of
cells.
To get the time value in Z1 try
=(SUMPRODUCT(--(ColorIndex(B1:O1)=3))*15)/24
this will work for the color 'red' (red = 3) - it sums all red
colored cells. For more information abour color values have a look
at
http://www.mvps.org/dmcritchie/excel/colors.htm
Frank
"Frank Kabel" skrev i meddelandet
...
Hi
for the first question:
- format your cells with a time format (Format - Cells - Time)
They
should look like '10:00 AM'
The second one: Yes it is possible but you'll need VBA. e.g.
- put the code found below in a module of your workbook
- if you want to color in the range A1:Z1 with a color (lets say
red)
and count the number of colored cells use
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))
- to get a time value use the formula
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))/24
and format this target cell as time
------Function Colorindex - Repost from Bob Phillips
'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
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
--
Regards
Frank Kabel
Frankfurt, Germany
Jonsson wrote:
Hi Frank
Thanks for your help!!
But I get the value -1499?! It´s probably because I have the
wrong
format of the cell D1.
Can you please tell me what format it should be?
I suppose you don´t think my second whish was possible?
//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
try for the first one
=(C1<B1)+C1-B1
--
Regards
Frank Kabel
Frankfurt, Germany
Jonsson wrote:
Hi all,
Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9
hours
in cell D1?
And/Or.....
color 9 cells, (just as the number of hours), and get the time
value 2200 and 0700.
I hope there is........
Thanks for any help
//Thomas