timeformatting and colored cells
 

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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

Hi Frank!

First...
Is it impossible to have the time as "2200"? It would be easier to write
than to write "22:00"......................?

Second.....
I assume I´m supposed to drive the code, but when I do, I get the question
"Makroname".

Third.........

When I paste the formula in AA1 I get "Name?"

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.

I really appreaciate your help and hope you can help me with this!
Thomas, Sweden

"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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

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

timeformatting and colored cells
 

Frank, I really appreciate your effort to help me, now it works!!
THANKS!

I will take a look at the websites as you suggested!!

//Thomas

"Frank Kabel" skrev i meddelandet
...
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

timeformatting and colored cells
 

Hi
good to hear that it works now for you
Was it the different function name for SUMPRODUCT in your localized
Excel version. If yes you may contact Norman Harker (search in this NG
for his name) as ask him for his function list (includes also
translations for English<-Swedish).
I assume you have a 'good home' so it should be no problem to get the
list from Norman :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Frank, I really appreciate your effort to help me, now it works!!
THANKS!

I will take a look at the websites as you suggested!!

//Thomas

"Frank Kabel" skrev i meddelandet
...
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