[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Conditional Computing

aung

12/12/2006 1:55:00 PM

Hi,

I am not sure Excel 2003 can do this or not. Here is what I would like
to do.
I have a column (say column A) with some numbers. Some of the cells are
colored green and some are colored red.
I would like to add all the cells colored in red at cell B1 and add all
the cells colored in green at cell C1. The color may be changed
dynamically by means of conditional formatting.

Any idea?

Thanks.
19 Answers

John Coleman

12/12/2006 2:14:00 PM

0

Hi,

You can do this in VBA of course (hint: use the worksheet_calculate
event). But - before you go that rout, have you considered using the
conditional sum wizard? It is designed to create conditional sum
formulas using the same sorts of criteria as are used in conditional
formatting. From your problem description, this seems like a good
possibility. It is an add in. Go to tools -> add ins and install it if
it isn't already. This may be better than a VBA approach since, for
example, the code might break if you decide you want to use different
colors in the conditional formatting. It would probably also be quicker
if you are talking about a large number of cells.

HTH

-John Coleman

aung@sinaptiq.com wrote:
> Hi,
>
> I am not sure Excel 2003 can do this or not. Here is what I would like
> to do.
> I have a column (say column A) with some numbers. Some of the cells are
> colored green and some are colored red.
> I would like to add all the cells colored in red at cell B1 and add all
> the cells colored in green at cell C1. The color may be changed
> dynamically by means of conditional formatting.
>
> Any idea?
>
> Thanks.

Bob Phillips

12/12/2006 2:49:00 PM

0

SUM them based upon the same condition that is applied to CF.

For instance, if the CF is greater than 10, then use

=SUMIF(A1:A10,">10")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


<aung@sinaptiq.com> wrote in message
news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
> Hi,
>
> I am not sure Excel 2003 can do this or not. Here is what I would like
> to do.
> I have a column (say column A) with some numbers. Some of the cells are
> colored green and some are colored red.
> I would like to add all the cells colored in red at cell B1 and add all
> the cells colored in green at cell C1. The color may be changed
> dynamically by means of conditional formatting.
>
> Any idea?
>
> Thanks.
>


NickH

12/12/2006 3:13:00 PM

0

I think John's probably right, especially if you are already using
Excel's built-in Conditional Formatting.

However, should you decide to go the VBA route the following functions
may be of some help.

NB. These functions will NOT detect a cell interior colour that has
been set using conditional formatting. To do that you would need to
write code to interrogate the condtional format settings, in which case
you might as well write your own conditional formatting routine (yeah,
listen to John).

Still reading? Okay go here for some Conditional Format macro
suggestions...

http://www.mvps.org/dmcritchie/excel/c...

Here's a function to do the summing...

Public Function ColorSum(mRng As Range, mColor As Integer) As Single
Dim mTot As Single
Dim c As Range


For Each c In mRng
If IsNumeric(c.Value) Then
If c.Interior.ColorIndex = mColor Then
mTot = mTot + c.Value
End If
End If
Next c

ColorSum = mTot

End Function

To use enter something like =ColorSum(A1:A20,10) into a cell.

And here's a function to return the interior colour index of a cell...

Public Function GetColorIndex(mCell As Range) As Integer
''' Quick check to find the interior color of a cell. _
If multiple cells selected only top left examined

GetColorIndex = mCell.Range("A1").Interior.ColorIndex
End Function

To use enter something like =GetColorIndex(A3) into a cell.


Hope you listened to John - I'm just killing time. ?;^)

NickH

John Coleman

12/12/2006 3:14:00 PM

0

Bob,

Your advice is probably the way to go, but do you have any idea why the
following doesn't work as intended?

Function ColorSum(R As Range, i As Long) As Variant
Dim sum As Variant
Dim cl As Range
For Each cl In R.Cells
If cl.Interior.ColorIndex = i Then
sum = sum + cl.Value
End If
Next cl
ColorSum = sum
End Function

Sub RedGreenSums()
Dim R As Range
Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
Range("B1").Value = ColorSum(R, 3)
Range("C1").Value = ColorSum(R, 10)
End Sub

Private Sub Worksheet_Calculate()
RedGreenSums
End Sub

'For debugging purposes:

Sub ShowIndex()
On Error Resume Next
MsgBox Selection.Interior.ColorIndex
End Sub


It works fine if the colors are manually set. But - if they are set by
conditional formatting then wierd things happen. I set up a trial sheet
in which the first 10 cells of column A were colored green for positive
values and red for negative values. When I actually change the values
from positive to negative or vice versa I get (when I run the ShowIndex
sub on various cells) that all colorindices are -4142. Maybe that is
some alias for xlAutomatic or something like that and the conditional
formatting in effect doesn't change the color per se but changes the
meaning of automatic for that cell. But then - this raises the
question: how can you determine the color of a cell if its color has
been set by conditional formatting? I guess you would have to determine
programmatically which condition applies and go inside the
corresponding formatcondition object - which seems like a lot of effort
to read what should be an easy property to read off.

-John Coleman

Bob Phillips wrote:
> SUM them based upon the same condition that is applied to CF.
>
> For instance, if the CF is greater than 10, then use
>
> =SUMIF(A1:A10,">10")
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> <aung@sinaptiq.com> wrote in message
> news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
> > Hi,
> >
> > I am not sure Excel 2003 can do this or not. Here is what I would like
> > to do.
> > I have a column (say column A) with some numbers. Some of the cells are
> > colored green and some are colored red.
> > I would like to add all the cells colored in red at cell B1 and add all
> > the cells colored in green at cell C1. The color may be changed
> > dynamically by means of conditional formatting.
> >
> > Any idea?
> >
> > Thanks.
> >

John Coleman

12/12/2006 3:20:00 PM

0

A few seconds after I posted I realized that it is obvious that
conditional formatting shouldn't change the colorindex - this allows
the original colorindex to stick around as a default color when none of
the conditions apply. It would be nice if cells had a
DisplayedColorIndex property in addition to a ColorIndex one. I'll have
to write my own function. No need to answer my previous post.

John Coleman wrote:
> Bob,
>
> Your advice is probably the way to go, but do you have any idea why the
> following doesn't work as intended?
>
> Function ColorSum(R As Range, i As Long) As Variant
> Dim sum As Variant
> Dim cl As Range
> For Each cl In R.Cells
> If cl.Interior.ColorIndex = i Then
> sum = sum + cl.Value
> End If
> Next cl
> ColorSum = sum
> End Function
>
> Sub RedGreenSums()
> Dim R As Range
> Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
> Range("B1").Value = ColorSum(R, 3)
> Range("C1").Value = ColorSum(R, 10)
> End Sub
>
> Private Sub Worksheet_Calculate()
> RedGreenSums
> End Sub
>
> 'For debugging purposes:
>
> Sub ShowIndex()
> On Error Resume Next
> MsgBox Selection.Interior.ColorIndex
> End Sub
>
>
> It works fine if the colors are manually set. But - if they are set by
> conditional formatting then wierd things happen. I set up a trial sheet
> in which the first 10 cells of column A were colored green for positive
> values and red for negative values. When I actually change the values
> from positive to negative or vice versa I get (when I run the ShowIndex
> sub on various cells) that all colorindices are -4142. Maybe that is
> some alias for xlAutomatic or something like that and the conditional
> formatting in effect doesn't change the color per se but changes the
> meaning of automatic for that cell. But then - this raises the
> question: how can you determine the color of a cell if its color has
> been set by conditional formatting? I guess you would have to determine
> programmatically which condition applies and go inside the
> corresponding formatcondition object - which seems like a lot of effort
> to read what should be an easy property to read off.
>
> -John Coleman
>
> Bob Phillips wrote:
> > SUM them based upon the same condition that is applied to CF.
> >
> > For instance, if the CF is greater than 10, then use
> >
> > =SUMIF(A1:A10,">10")
> >
> > --
> > ---
> > HTH
> >
> > Bob
> >
> > (change the xxxx to gmail if mailing direct)
> >
> >
> > <aung@sinaptiq.com> wrote in message
> > news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
> > > Hi,
> > >
> > > I am not sure Excel 2003 can do this or not. Here is what I would like
> > > to do.
> > > I have a column (say column A) with some numbers. Some of the cells are
> > > colored green and some are colored red.
> > > I would like to add all the cells colored in red at cell B1 and add all
> > > the cells colored in green at cell C1. The color may be changed
> > > dynamically by means of conditional formatting.
> > >
> > > Any idea?
> > >
> > > Thanks.
> > >

Bob Phillips

12/12/2006 3:30:00 PM

0

John,

They do, that is what the CF colour is. It is difficult, but not impossible
to get the CF colour. See http://xldynamic.com/source/xld.CFCondi...

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"John Coleman" <jcoleman@franciscan.edu> wrote in message
news:1165936777.752042.263390@j72g2000cwa.googlegroups.com...
>A few seconds after I posted I realized that it is obvious that
> conditional formatting shouldn't change the colorindex - this allows
> the original colorindex to stick around as a default color when none of
> the conditions apply. It would be nice if cells had a
> DisplayedColorIndex property in addition to a ColorIndex one. I'll have
> to write my own function. No need to answer my previous post.
>
> John Coleman wrote:
>> Bob,
>>
>> Your advice is probably the way to go, but do you have any idea why the
>> following doesn't work as intended?
>>
>> Function ColorSum(R As Range, i As Long) As Variant
>> Dim sum As Variant
>> Dim cl As Range
>> For Each cl In R.Cells
>> If cl.Interior.ColorIndex = i Then
>> sum = sum + cl.Value
>> End If
>> Next cl
>> ColorSum = sum
>> End Function
>>
>> Sub RedGreenSums()
>> Dim R As Range
>> Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
>> Range("B1").Value = ColorSum(R, 3)
>> Range("C1").Value = ColorSum(R, 10)
>> End Sub
>>
>> Private Sub Worksheet_Calculate()
>> RedGreenSums
>> End Sub
>>
>> 'For debugging purposes:
>>
>> Sub ShowIndex()
>> On Error Resume Next
>> MsgBox Selection.Interior.ColorIndex
>> End Sub
>>
>>
>> It works fine if the colors are manually set. But - if they are set by
>> conditional formatting then wierd things happen. I set up a trial sheet
>> in which the first 10 cells of column A were colored green for positive
>> values and red for negative values. When I actually change the values
>> from positive to negative or vice versa I get (when I run the ShowIndex
>> sub on various cells) that all colorindices are -4142. Maybe that is
>> some alias for xlAutomatic or something like that and the conditional
>> formatting in effect doesn't change the color per se but changes the
>> meaning of automatic for that cell. But then - this raises the
>> question: how can you determine the color of a cell if its color has
>> been set by conditional formatting? I guess you would have to determine
>> programmatically which condition applies and go inside the
>> corresponding formatcondition object - which seems like a lot of effort
>> to read what should be an easy property to read off.
>>
>> -John Coleman
>>
>> Bob Phillips wrote:
>> > SUM them based upon the same condition that is applied to CF.
>> >
>> > For instance, if the CF is greater than 10, then use
>> >
>> > =SUMIF(A1:A10,">10")
>> >
>> > --
>> > ---
>> > HTH
>> >
>> > Bob
>> >
>> > (change the xxxx to gmail if mailing direct)
>> >
>> >
>> > <aung@sinaptiq.com> wrote in message
>> > news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
>> > > Hi,
>> > >
>> > > I am not sure Excel 2003 can do this or not. Here is what I would
>> > > like
>> > > to do.
>> > > I have a column (say column A) with some numbers. Some of the cells
>> > > are
>> > > colored green and some are colored red.
>> > > I would like to add all the cells colored in red at cell B1 and add
>> > > all
>> > > the cells colored in green at cell C1. The color may be changed
>> > > dynamically by means of conditional formatting.
>> > >
>> > > Any idea?
>> > >
>> > > Thanks.
>> > >
>


John Coleman

12/12/2006 4:09:00 PM

0

Bob,

Impressive. I find it amazing how involved the code is. Conditional
formats ultimately involve a simple boolean value that the application
has stored *somewhere*, and given how important conditional formatting
is in many spreadsheets it is disappointing that this value is not
exposed in the object model. You are to be commended in being able to
discover a work-around. Thanks for the link!

-John Coleman

Bob Phillips wrote:
> John,
>
> They do, that is what the CF colour is. It is difficult, but not impossible
> to get the CF colour. See http://xldynamic.com/source/xld.CFCondi...
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "John Coleman" <jcoleman@franciscan.edu> wrote in message
> news:1165936777.752042.263390@j72g2000cwa.googlegroups.com...
> >A few seconds after I posted I realized that it is obvious that
> > conditional formatting shouldn't change the colorindex - this allows
> > the original colorindex to stick around as a default color when none of
> > the conditions apply. It would be nice if cells had a
> > DisplayedColorIndex property in addition to a ColorIndex one. I'll have
> > to write my own function. No need to answer my previous post.
> >
> > John Coleman wrote:
> >> Bob,
> >>
> >> Your advice is probably the way to go, but do you have any idea why the
> >> following doesn't work as intended?
> >>
> >> Function ColorSum(R As Range, i As Long) As Variant
> >> Dim sum As Variant
> >> Dim cl As Range
> >> For Each cl In R.Cells
> >> If cl.Interior.ColorIndex = i Then
> >> sum = sum + cl.Value
> >> End If
> >> Next cl
> >> ColorSum = sum
> >> End Function
> >>
> >> Sub RedGreenSums()
> >> Dim R As Range
> >> Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
> >> Range("B1").Value = ColorSum(R, 3)
> >> Range("C1").Value = ColorSum(R, 10)
> >> End Sub
> >>
> >> Private Sub Worksheet_Calculate()
> >> RedGreenSums
> >> End Sub
> >>
> >> 'For debugging purposes:
> >>
> >> Sub ShowIndex()
> >> On Error Resume Next
> >> MsgBox Selection.Interior.ColorIndex
> >> End Sub
> >>
> >>
> >> It works fine if the colors are manually set. But - if they are set by
> >> conditional formatting then wierd things happen. I set up a trial sheet
> >> in which the first 10 cells of column A were colored green for positive
> >> values and red for negative values. When I actually change the values
> >> from positive to negative or vice versa I get (when I run the ShowIndex
> >> sub on various cells) that all colorindices are -4142. Maybe that is
> >> some alias for xlAutomatic or something like that and the conditional
> >> formatting in effect doesn't change the color per se but changes the
> >> meaning of automatic for that cell. But then - this raises the
> >> question: how can you determine the color of a cell if its color has
> >> been set by conditional formatting? I guess you would have to determine
> >> programmatically which condition applies and go inside the
> >> corresponding formatcondition object - which seems like a lot of effort
> >> to read what should be an easy property to read off.
> >>
> >> -John Coleman
> >>
> >> Bob Phillips wrote:
> >> > SUM them based upon the same condition that is applied to CF.
> >> >
> >> > For instance, if the CF is greater than 10, then use
> >> >
> >> > =SUMIF(A1:A10,">10")
> >> >
> >> > --
> >> > ---
> >> > HTH
> >> >
> >> > Bob
> >> >
> >> > (change the xxxx to gmail if mailing direct)
> >> >
> >> >
> >> > <aung@sinaptiq.com> wrote in message
> >> > news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
> >> > > Hi,
> >> > >
> >> > > I am not sure Excel 2003 can do this or not. Here is what I would
> >> > > like
> >> > > to do.
> >> > > I have a column (say column A) with some numbers. Some of the cells
> >> > > are
> >> > > colored green and some are colored red.
> >> > > I would like to add all the cells colored in red at cell B1 and add
> >> > > all
> >> > > the cells colored in green at cell C1. The color may be changed
> >> > > dynamically by means of conditional formatting.
> >> > >
> >> > > Any idea?
> >> > >
> >> > > Thanks.
> >> > >
> >

Bob Phillips

12/12/2006 4:32:00 PM

0

John,

The big problem lies in what is actually a feature of CF. If you select a
range of cells and apply CF, it will adjust any formula presented relative
to the position of the cell in the selection. This is a very convenient way
of selecting a range and using say

=AND(A1>B1,C1>TODAY())

next cell will adjust the row or column as appropriate. As I said, it is
very useful as it allows you to setup multiple cells at once, but the CF is
relative. And when you are not in that cell, you have to make an adjustment
for where the CF is being evaluated from. Therein lies the difficulty, and
thus the coding complexity.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"John Coleman" <jcoleman@franciscan.edu> wrote in message
news:1165939757.575852.59410@l12g2000cwl.googlegroups.com...
> Bob,
>
> Impressive. I find it amazing how involved the code is. Conditional
> formats ultimately involve a simple boolean value that the application
> has stored *somewhere*, and given how important conditional formatting
> is in many spreadsheets it is disappointing that this value is not
> exposed in the object model. You are to be commended in being able to
> discover a work-around. Thanks for the link!
>
> -John Coleman
>
> Bob Phillips wrote:
>> John,
>>
>> They do, that is what the CF colour is. It is difficult, but not
>> impossible
>> to get the CF colour. See
>> http://xldynamic.com/source/xld.CFCondi...
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "John Coleman" <jcoleman@franciscan.edu> wrote in message
>> news:1165936777.752042.263390@j72g2000cwa.googlegroups.com...
>> >A few seconds after I posted I realized that it is obvious that
>> > conditional formatting shouldn't change the colorindex - this allows
>> > the original colorindex to stick around as a default color when none of
>> > the conditions apply. It would be nice if cells had a
>> > DisplayedColorIndex property in addition to a ColorIndex one. I'll have
>> > to write my own function. No need to answer my previous post.
>> >
>> > John Coleman wrote:
>> >> Bob,
>> >>
>> >> Your advice is probably the way to go, but do you have any idea why
>> >> the
>> >> following doesn't work as intended?
>> >>
>> >> Function ColorSum(R As Range, i As Long) As Variant
>> >> Dim sum As Variant
>> >> Dim cl As Range
>> >> For Each cl In R.Cells
>> >> If cl.Interior.ColorIndex = i Then
>> >> sum = sum + cl.Value
>> >> End If
>> >> Next cl
>> >> ColorSum = sum
>> >> End Function
>> >>
>> >> Sub RedGreenSums()
>> >> Dim R As Range
>> >> Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
>> >> Range("B1").Value = ColorSum(R, 3)
>> >> Range("C1").Value = ColorSum(R, 10)
>> >> End Sub
>> >>
>> >> Private Sub Worksheet_Calculate()
>> >> RedGreenSums
>> >> End Sub
>> >>
>> >> 'For debugging purposes:
>> >>
>> >> Sub ShowIndex()
>> >> On Error Resume Next
>> >> MsgBox Selection.Interior.ColorIndex
>> >> End Sub
>> >>
>> >>
>> >> It works fine if the colors are manually set. But - if they are set by
>> >> conditional formatting then wierd things happen. I set up a trial
>> >> sheet
>> >> in which the first 10 cells of column A were colored green for
>> >> positive
>> >> values and red for negative values. When I actually change the values
>> >> from positive to negative or vice versa I get (when I run the
>> >> ShowIndex
>> >> sub on various cells) that all colorindices are -4142. Maybe that is
>> >> some alias for xlAutomatic or something like that and the conditional
>> >> formatting in effect doesn't change the color per se but changes the
>> >> meaning of automatic for that cell. But then - this raises the
>> >> question: how can you determine the color of a cell if its color has
>> >> been set by conditional formatting? I guess you would have to
>> >> determine
>> >> programmatically which condition applies and go inside the
>> >> corresponding formatcondition object - which seems like a lot of
>> >> effort
>> >> to read what should be an easy property to read off.
>> >>
>> >> -John Coleman
>> >>
>> >> Bob Phillips wrote:
>> >> > SUM them based upon the same condition that is applied to CF.
>> >> >
>> >> > For instance, if the CF is greater than 10, then use
>> >> >
>> >> > =SUMIF(A1:A10,">10")
>> >> >
>> >> > --
>> >> > ---
>> >> > HTH
>> >> >
>> >> > Bob
>> >> >
>> >> > (change the xxxx to gmail if mailing direct)
>> >> >
>> >> >
>> >> > <aung@sinaptiq.com> wrote in message
>> >> > news:1165931679.875819.99850@l12g2000cwl.googlegroups.com...
>> >> > > Hi,
>> >> > >
>> >> > > I am not sure Excel 2003 can do this or not. Here is what I would
>> >> > > like
>> >> > > to do.
>> >> > > I have a column (say column A) with some numbers. Some of the
>> >> > > cells
>> >> > > are
>> >> > > colored green and some are colored red.
>> >> > > I would like to add all the cells colored in red at cell B1 and
>> >> > > add
>> >> > > all
>> >> > > the cells colored in green at cell C1. The color may be changed
>> >> > > dynamically by means of conditional formatting.
>> >> > >
>> >> > > Any idea?
>> >> > >
>> >> > > Thanks.
>> >> > >
>> >
>


NickH

12/12/2006 5:30:00 PM

0

Very useful stuff Bob, Thanks.

NickH

aung

12/13/2006 12:49:00 AM

0

Hi Guys,

Thank you so much for all your help. Very impressive indeed.

Aung

NickH wrote:
> Very useful stuff Bob, Thanks.
>
> NickH