SumByColor Macro...

I am using the following Macro - the formula I use to access the macro is

=sumBYCOLOR(C13:H22,3,TRUE)

The problem I am having is that the formula does not automatically calculate
no information inputted into the excel sheet. I have to go to the cell with
the formula and place my cursor at the end of the formula and hit enter for
it to adjust with the new information. Any help?

Macro is:Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

Hi Murph,

Try:

Tools | Options | Calculation | check 'Automatic'


---
Regards,
Norman



"Murph" <[email protected]> wrote in message
news:[email protected]...
>I am using the following Macro - the formula I use to access the macro is
>
> =sumBYCOLOR(C13:H22,3,TRUE)
>
> The problem I am having is that the formula does not automatically
> calculate
> no information inputted into the excel sheet. I have to go to the cell
> with
> the formula and place my cursor at the end of the formula and hit enter
> for
> it to adjust with the new information. Any help?
>
> Macro is:Function SumByColor(InRange As Range, WhatColorIndex As Integer,
> _
> Optional OfText As Boolean = False) As Double
> '
> ' This function return the SUM of the values of cells in
> ' InRange with a background color, or if OfText is True a
> ' font color, equal to WhatColorIndex.
> '
> Dim Rng As Range
> Dim OK As Boolean
>
> Application.Volatile True
> For Each Rng In InRange.Cells
> If OfText = True Then
> OK = (Rng.Font.ColorIndex = WhatColorIndex)
> Else
> OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> End If
> If OK And IsNumeric(Rng.Value) Then
> SumByColor = SumByColor + Rng.Value
> End If
> Next Rng
>
> End Function
>

And just to add, if you are just changing colors, no formula is going to
update until there is a calculate (which is not triggered by changing
colors).

--
Regards,
Tom Ogilvy

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Murph,
>
> Try:
>
> Tools | Options | Calculation | check 'Automatic'
>
>
> ---
> Regards,
> Norman
>
>
>
> "Murph" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the following Macro - the formula I use to access the macro is
> >
> > =sumBYCOLOR(C13:H22,3,TRUE)
> >
> > The problem I am having is that the formula does not automatically
> > calculate
> > no information inputted into the excel sheet. I have to go to the cell
> > with
> > the formula and place my cursor at the end of the formula and hit enter
> > for
> > it to adjust with the new information. Any help?
> >
> > Macro is:Function SumByColor(InRange As Range, WhatColorIndex As
Integer,
> > _
> > Optional OfText As Boolean = False) As Double
> > '
> > ' This function return the SUM of the values of cells in
> > ' InRange with a background color, or if OfText is True a
> > ' font color, equal to WhatColorIndex.
> > '
> > Dim Rng As Range
> > Dim OK As Boolean
> >
> > Application.Volatile True
> > For Each Rng In InRange.Cells
> > If OfText = True Then
> > OK = (Rng.Font.ColorIndex = WhatColorIndex)
> > Else
> > OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> > End If
> > If OK And IsNumeric(Rng.Value) Then
> > SumByColor = SumByColor + Rng.Value
> > End If
> > Next Rng
> >
> > End Function
> >
>
>

Changing the color doesn't cause excel to recalculate.

You'll need to recalculate to make sure your values are correct.

Hitting the F9 may be enough.

Murph wrote:
>
> I am using the following Macro - the formula I use to access the macro is
>
> =sumBYCOLOR(C13:H22,3,TRUE)
>
> The problem I am having is that the formula does not automatically calculate
> no information inputted into the excel sheet. I have to go to the cell with
> the formula and place my cursor at the end of the formula and hit enter for
> it to adjust with the new information. Any help?
>
> Macro is:Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
> Optional OfText As Boolean = False) As Double
> '
> ' This function return the SUM of the values of cells in
> ' InRange with a background color, or if OfText is True a
> ' font color, equal to WhatColorIndex.
> '
> Dim Rng As Range
> Dim OK As Boolean
>
> Application.Volatile True
> For Each Rng In InRange.Cells
> If OfText = True Then
> OK = (Rng.Font.ColorIndex = WhatColorIndex)
> Else
> OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> End If
> If OK And IsNumeric(Rng.Value) Then
> SumByColor = SumByColor + Rng.Value
> End If
> Next Rng
>
> End Function

--

Dave Peterson

Norman -

I checked that and it is selected currently.

I assume this basically has something to do with Excel not recognizing
colors as part of a formula argument. If I click in another cell and just
type in 0 and hit enter it then recalculates and finds the 'red' and 'black'
entries. Just seems odd - and of course not as user friendly as I would like.

Also if I delete an entry in the cell it automatically recalculates. So
basically the problem happens when I take the cell font color from black to
red.

Thanks for the help.

"Norman Jones" wrote:

> Hi Murph,
>
> Try:
>
> Tools | Options | Calculation | check 'Automatic'
>
>
> ---
> Regards,
> Norman
>
>
>
> "Murph" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the following Macro - the formula I use to access the macro is
> >
> > =sumBYCOLOR(C13:H22,3,TRUE)
> >
> > The problem I am having is that the formula does not automatically
> > calculate
> > no information inputted into the excel sheet. I have to go to the cell
> > with
> > the formula and place my cursor at the end of the formula and hit enter
> > for
> > it to adjust with the new information. Any help?
> >
> > Macro is:Function SumByColor(InRange As Range, WhatColorIndex As Integer,
> > _
> > Optional OfText As Boolean = False) As Double
> > '
> > ' This function return the SUM of the values of cells in
> > ' InRange with a background color, or if OfText is True a
> > ' font color, equal to WhatColorIndex.
> > '
> > Dim Rng As Range
> > Dim OK As Boolean
> >
> > Application.Volatile True
> > For Each Rng In InRange.Cells
> > If OfText = True Then
> > OK = (Rng.Font.ColorIndex = WhatColorIndex)
> > Else
> > OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> > End If
> > If OK And IsNumeric(Rng.Value) Then
> > SumByColor = SumByColor + Rng.Value
> > End If
> > Next Rng
> >
> > End Function
> >
>
>
>

As stated by several people in response to your post, a color change does
not cause a recalculation to occur. (Excel does not inherently support
color as a discriminator in any type of calculation).

--
Regards,
Tom Ogilvy

"Murph" <[email protected]> wrote in message
news:[email protected]...
> Norman -
>
> I checked that and it is selected currently.
>
> I assume this basically has something to do with Excel not recognizing
> colors as part of a formula argument. If I click in another cell and just
> type in 0 and hit enter it then recalculates and finds the 'red' and
'black'
> entries. Just seems odd - and of course not as user friendly as I would
like.
>
> Also if I delete an entry in the cell it automatically recalculates. So
> basically the problem happens when I take the cell font color from black
to
> red.
>
> Thanks for the help.
>
> "Norman Jones" wrote:
>
> > Hi Murph,
> >
> > Try:
> >
> > Tools | Options | Calculation | check 'Automatic'
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Murph" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am using the following Macro - the formula I use to access the macro
is
> > >
> > > =sumBYCOLOR(C13:H22,3,TRUE)
> > >
> > > The problem I am having is that the formula does not automatically
> > > calculate
> > > no information inputted into the excel sheet. I have to go to the cell
> > > with
> > > the formula and place my cursor at the end of the formula and hit
enter
> > > for
> > > it to adjust with the new information. Any help?
> > >
> > > Macro is:Function SumByColor(InRange As Range, WhatColorIndex As
Integer,
> > > _
> > > Optional OfText As Boolean = False) As Double
> > > '
> > > ' This function return the SUM of the values of cells in
> > > ' InRange with a background color, or if OfText is True a
> > > ' font color, equal to WhatColorIndex.
> > > '
> > > Dim Rng As Range
> > > Dim OK As Boolean
> > >
> > > Application.Volatile True
> > > For Each Rng In InRange.Cells
> > > If OfText = True Then
> > > OK = (Rng.Font.ColorIndex = WhatColorIndex)
> > > Else
> > > OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> > > End If
> > > If OK And IsNumeric(Rng.Value) Then
> > > SumByColor = SumByColor + Rng.Value
> > > End If
> > > Next Rng
> > >
> > > End Function
> > >
> >
> >
> >

"Murph" <[email protected]> wrote in message
news:[email protected]...
> I am using the following Macro - the formula I use to access the macro is
>
> =sumBYCOLOR(C13:H22,3,TRUE)
>
> The problem I am having is that the formula does not automatically
calculate
> no information inputted into the excel sheet. I have to go to the cell
with
> the formula and place my cursor at the end of the formula and hit enter
for
> it to adjust with the new information. Any help?
>
> Macro is:Function SumByColor(InRange As Range, WhatColorIndex As Integer,
_
> Optional OfText As Boolean = False) As Double
> '
> ' This function return the SUM of the values of cells in
> ' InRange with a background color, or if OfText is True a
> ' font color, equal to WhatColorIndex.
> '
> Dim Rng As Range
> Dim OK As Boolean
>
> Application.Volatile True
> For Each Rng In InRange.Cells
> If OfText = True Then
> OK = (Rng.Font.ColorIndex = WhatColorIndex)
> Else
> OK = (Rng.Interior.ColorIndex = WhatColorIndex)
> End If
> If OK And IsNumeric(Rng.Value) Then
> SumByColor = SumByColor + Rng.Value
> End If
> Next Rng
>
> End Function
>

I modified the code a bit so a user can select the color by incduing a cell
range reference:

Function SumByColor(InRange As Range, rColor As Range, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
' the user can select the color by including a reference to a call or a
range of cells
' of the appropriate color

Dim Rng As Range
Dim iClr As Integer ' the color to sum
Dim iCellClr ' the color of the cell
Dim OK As Boolean

Application.Volatile True

If OfText = True Then
iClr = rColor.Font.ColorIndex
Else
iClr = rColor.Interior.ColorIndex
End If

For Each Rng In InRange.Cells
If OfText = True Then
iCellClr = Rng.Font.ColorIndex
Else
iCellClr = Rng.Interior.ColorIndex
End If

If IsNumeric(Rng.Value) And iCellClr = iClr Then
SumByColor = SumByColor + Rng.Value
End If

Next Rng

Application.Calculate

End Function


Comments?