Analysis for Excel 2.4 - Set custom style for hierarchy nodes and data

[Former Member]

Dear Experts,

I have the task to set a custom style on a workbook based on analysis for excel 2.4.

I need to set a format that applies to members AND data of all the nodes of a hierarchy except leaves, like in following image:

I want this format to be dynamic, so that, once applied, all present and future nodes of the hierarchy will obtain this formatting.

Thank you in advance, Francesco

[TammyPowlas]

Francesco - have you tried the Analysis Cell styles, and where do they not work for you?

[Former Member]

Dear Cesar,

unfortunately I did not find any solution to this issue.

Regards, Francesco

[Former Member]

Hello Tammy,

first of all thank you for answering, if I use Analysis Cell for Hierarchy, for example SapHierarchyCell1, what I get is that I apply a format only to Hierarchy member nodes:

I also need to apply the formatting to the values of the keyfigures corresponding to those nodes, so, in my screenshot, values in the red circle should be yellow as well.

Do you know how to reach this result?

Thank you, Francesco

[former_member283567]

Thank you very much for the example VBA code Lamine
As I am not a VBA expert, this was exactly the type of example I was looking for.

[former_member1130280]

Here's a solution with a small VBA code on Sub Callback_AfterRedisplay()

Before you must create the styles for key figures for each hierarchy level style (SAPHierarchyCellX)

By naming convention the styles for key figures are named "SAPHierarchyCellX KF"

VBA code :

Public Sub Callback_AfterRedisplay()

Dim LastCol As Long

Dim lastrow As Long

LastCol = Sheets(2).Cells(3, Columns.Count).End(xlToLeft).Column

lastrow = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row

For I = 3 To lastrow

For J = 3 To LastCol

If Sheets(2).Cells(I, J).Style <> "Empty" And Sheets(2).Cells(I, J).Style <> "Empty2" Then

If Sheets(2).Cells(I, 1).Style = "SAPHierarchyCell1" Or Sheets(2).Cells(I, 1).Style = "SAPHierarchyCell2" Then

Sheets(2).Cells(I, J).Style = Sheets(2).Cells(I, 1).Style & " KF"

End If

End If

Next J

Next I

End Sub

[former_member1130280]

For determining the last row and last column you should use these 2 lines

lastrow = Range("SAPCrosstab1").Rows.Count + Range("SAPCrosstab1").Row - 1

LastCol = Range("SAPCrosstab1").Columns.Count + Range("SAPCrosstab1").Column - 1

Good luck

[chernandezmarti]

Francesco, did you find out how to fix this? I have the same requirement. As there is only one SAPMemberCell, then the format is applied to several nodes.

[Former Member]

Thank you Tammy, I already tryied modifying SAPMemberCell but if I do that I end up getting all member cells highlighted instead of only member cells corresponding to hierarchy nodes (not leaves):

So, in my screenshot, values in the green circle are correctly paintend in yellow, because they correspond to a hierarchy node, but values in the red circle are wrongly yellow, because they correspond to a hierarchy leaf.

Thank you, Francesco

[TammyPowlas]

Hello Francesco - those are Member Cell styles; go to the Home page and select the Cell Styles ribbon and you will see them: