EXCEL FEED: Re: [ExcelVBA] Pivot Table & Color Counting

Kamis, 05 November 2015

Re: [ExcelVBA] Pivot Table & Color Counting

David,

I am using "=CountByColor(E151,E8:E137)". Basics for me.

Thanks,

On Thursday, November 5, 2015 8:07 PM, "David Grugeon grugeon@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

I am not sure which of the two statements produced the error.

If it is producint\g the error for the address statement then you are not supplying the range when you call the function.

How are you calling the function? Is it used in a cell formula? If so what is the formula? If it is called from another procedure (Sub or Function) then what is the line calling it?

If you would like me to have a look at the workbook you are welcome to send it to me (grugeon AT gmail dot com) and I will find out what is going on.

Regards

David Grugeon

On 5 November 2015 at 12:08, Ty Bak tyrone_bak@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:

It is displaying "#VALUE!" in the box.   What is the next stage? Thx, Ty

On Wednesday, November 4, 2015 6:44 AM, "David Grugeon grugeon@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

Make it like this

Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile

Debug.Print CellColor.address
Debug.Print CellColor.Interior.ColorIndex
Stop

Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
    If ICol = TCell.Interior.ColorIndex Then
        CountByColor = CountByColor + 1
    End If
Next TCell
End Function

Run it and then in the VBA Editor see what is printed in the Immediate Window.

Tell us what is there and then we can go to the next stage.

Regards
David Grugeon

On 4 November 2015 at 21:35, Ty Bak tyrone_bak@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:

David,

This is 101 class for me. So, I need to replace ICol = CellColor.Interior.ColorIndex with ICol=Debug.Print CellColor.Interior.ColorIndex.

Is that how I should use the Debug.Print to figure out how to use the number values to resolve my problem?

Thanks,
Ty

On Tuesday, November 3, 2015 10:01 PM, "David Grugeon grugeon@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

I suspect the colour in the pivot table is conditional formatting. This is not the Interior.Color of the cell. If this is the problem you may have to uase the number value to identify the relevant cells.

I would put debug.pprint statements in the code to test the values like

Debug.Print CellColor.address
Debug.Print CellColor.Interior.ColorIndex

And see what you are looking at.

Regards
David Grugeon

On 4 November 2015 at 12:22, tyrone_bak@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:

Anyone,

I have a macro for counting colors that works on a normal worksheet.

Problem: Clever created Pivot table with colors(Green, Red, Black..etc) that have numbers and text when you hover over the color

Example: When cursor is over the Green value = 5(RED = 3) with text for the source data column and description

Problem #2: I was attempting to use the Color Macro for 2 days w/ no luck while placing the colors in other blocks outside of the pivot. It worked outside of the pivot table.

Any ideas on why the Color Macro is not working. The Macro is down below:

Thanks,
Ty

Here is the Macro:

Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
    If ICol = TCell.Interior.ColorIndex Then
        CountByColor = CountByColor + 1
    End If
Next TCell
End Function

__._,_.___

Posted by: Ty Bak <tyrone_bak@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group