[blujein]

Hello,

I have another conditional formatting issue in Microsoft Excel!

In a spreadsheet I'm working on at the moment I have a range of cells that I will, at times, manually give a green background. This is simply a visual aid. These cells also contain a value (a dollar amount). So, in one row, there might be three green cells and four normal cells (with no background), and at the end of that row there would be a cell that needs to calculate the total dollar amount of the cells in the row with a green background.

Can anyone provide me with instructions and information on how to accomplish this?

[Aj_old]

2 Questions:
What MS office are you using 2003 or 2007?
Cells get colored in green, based on some conditions, or it can be randomly? (You chose what cell to color and it don't depend on it's value)

[The Villan]

Using your example above Blujein.

The four normal cells - do they also contain values or are the cells blank?

[blujein]

The four cells without a background do have a value, yes.

Also, I'm running Microsoft Excel 2003 and the cells with the green background are not getting the green background via a conditional format; they're manually made green as it is purely a visual cue.

[Aj_old]

Originally Posted by blujein

the cells with the green background are not getting the green background via a conditional format; they're manually made green as it is purely a visual cue.

I understood this, but based on what are you coloring then?

[blujein]

I'm not sure that I understand what you mean - are you asking why I am making them have a green background? If so, it's because the spreadsheet contains financial information, and the cells with a green background show an amount that is scheduled for payment but not yet finalised. Once it has been finalised, I would remove the green background.

[Aj_old]

In this case you need a macro or an User Defined Function to do this!

[blujein]

Are you able to provide me with some instruction on how to do this?

[Aj_old]

put this in a standard module of the workbook you are working with:

Function Count_by_InteriorColor(ByRef rgRange As Range, ByRef ColorCriteria)
    Dim cel As Range
    Counter = 0
    colorID = ColorCriteria.Interior.ColorIndex
    For Each cel In rgRange
        If Not (IsEmpty(cel)) And cel.Interior.ColorIndex = colorID Then Counter = Counter + 1
    Next
    Count_by_InteriorColor = Counter
End Function

Got to excel, press the function sign and select the User Defined Category, select the function with name "Count_by_InteriorColor" select the range you wanna to count, and in second field ColorCriteria, select a cell that have the same background as the cells you need to be counted, and hit enter

To be able to use this in other workbooks to, paste the code, into a standard module in the personal.xls
the rest is the same, just function name will have personal.xls in front

[slurpee55]

bluejin, AJ, once again, has come through. I might suggest that you get a free add-in for Excel called ASAP Utilities from http://www.asap-utilities.com/ .
It will do many of the things you are wanting - for instance, it has a function called CountShades that will count all cells that are colored. If you only want to count certain colors. Many of it's abilities are buried as UDFs, so be sure to investigate those thoroughly once you install it.

[blujein]

Originally Posted by Aj_old

put this in a standard module of the workbook you are working with:

Function Count_by_InteriorColor(ByRef rgRange As Range, ByRef ColorCriteria)
    Dim cel As Range
    Counter = 0
    colorID = ColorCriteria.Interior.ColorIndex
    For Each cel In rgRange
        If Not (IsEmpty(cel)) And cel.Interior.ColorIndex = colorID Then Counter = Counter + 1
    Next
    Count_by_InteriorColor = Counter
End Function

Got to excel, press the function sign and select the User Defined Category, select the function with name "Count_by_InteriorColor" select the range you wanna to count, and in second field ColorCriteria, select a cell that have the same background as the cells you need to be counted, and hit enter

To be able to use this in other workbooks to, paste the code, into a standard module in the personal.xls
the rest is the same, just function name will have personal.xls in front

This here is great! The only other question I have is whether we can make it count the values in the green cells rather than just count how many green cells there are? For example, if there are dollar values in the green cells, the end cell with the above user defined function would provide the total of the values.

[Aj_old]

Originally Posted by blujein

This here is great! The only other question I have is whether we can make it count the values in the green cells rather than just count how many green cells there are? For example, if there are dollar values in the green cells, the end cell with the above user defined function would provide the total of the values.

Try this:

Function Sum_by_InteriorColor(ByRef rgRange As Range, ByRef ColorCriteria)
    Dim cel As Range
    Summ= 0
    colorID = ColorCriteria.Interior.ColorIndex
    For Each cel In rgRange
        If Not (IsEmpty(cel)) And cel.Interior.ColorIndex = colorID Then Summ= summ+ cel.value
    Next
    Sum_by_InteriorColor = Summ
End Function

[blujein]

That's perfect, however there is still one problem. When I remove the green background (which will happen when the amount is considered paid), the total doesn't reflect it by subtracting that amount - it stays with the total as if the cell still had a green background. What can be done to work around this?

[Aj_old]

Originally Posted by blujein

That's perfect, however there is still one problem. When I remove the green background (which will happen when the amount is considered paid), the total doesn't reflect it by subtracting that amount - it stays with the total as if the cell still had a green background. What can be done to work around this?

It's because the result is not updated automatically! You need to recalculate the sheet!

[blujein]

Oh okay - sorry, but how is that done?