Conditional Formatting for column and month

[Former Member]

Hi Experts

I need a way for conditional formatting to columns in my report.

For each selected month should paint a column.

Example 1: The user has selected the V01 version corresponding to 1 month - January.

Thus the January column should come blue. So far so good, can you make a simple formatting in cells.

Example 2: The user selects the V02 version for the month of February. Thus the January and February column should come in blue.

Example 3: The user selects version V03 corresponding to March.

And should paint the columns, January, February and March in blue.

I can do the conditional formatting and context works. But while updating my report it loses the formatting.

The solution that was arranged to put the same conditional formatting in display formats, but when inserting a formula it does not accept.

Any suggestions?

Thank you

[Former Member · Accepted solution]

Hi experts,

I managed to solve as follows:

Realized with a formula If and VLOOKUP in Version.

= IF (K13> VLOOKUP ($ J $ 4, $ A $ 11: $ D $ 21, 4, 0); $ J $ 4; "NA_VERSAO").

Putting NA_VERSION, got through the display formats paint the columns concerning the chosen user version.

I thank you all for your help.

[Former Member]

Hi Thais,

i think you can do the following:

1- Populate any value's in the cells above the columns based on your time selection

2- use these populated cells in the "EPM FORMATTING SHEET" via conditional formatting.

[former_member186338]

Hi Thais,

What do you mean by corresponding in:

"V01 version corresponding to 1 month - January"

Vadim

[Former Member]

Hi Vadim,

The following images:

The columns should be painted as selected version.

example:

V03 version - Match the columns of January, February, March and April.

When the user selects the V03 version, these speakers should come in blue.

If the user selects the V00 version, only to January column should come in blue.

If the user selects the V01 version, only the January and February columns, should come in blue.

If the user selects the V02 version, only the January, February and March columns, should come in blue.

The rest will remain in the original white and orange.

Thank you

[former_member186338]

Hi Thais,

Sorry, but I am asking where the relation between version and months is stored? In some property or?

Vadim

[Former Member]

Hi Vadim,

Sorry, it is stored in BW - SAP Hana.

Thank you

[former_member186338]

But how? I have to repeat, is it a property of version or??? How the system will understand that version V02 has 2 months to be colored in blue?

[Former Member]

The period is a version property.

[Former Member]

Hi Thais,

Are you passing excel formatting in your report??

Regards,

Meenu

[Former Member]

Hi Meenu

  I'm using the EPM report formatting and excel.

I tried to make a macro run too.

But when updating loses the reference.

Following code:

Private Sub Worksheet_Change (ByVal Target As Range)

'Validates that was enacted i4 cell

If Target.Address = "$ I $ 4" Then

     'Colours range from white

     'Record a macro with its original formatting and replace this snippet

     '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''

     Range ("I11: T11"). Select

     with Selection.Interior

         . = Pattern xlSolid

         . PatternColorIndex = xlAutomatic

         . ThemeColor = xlThemeColorDark1

         . TintAndShade = 0

         . PatternTintAndShade = 0

     end With

     Range ("I12: T20"). Select

     with Selection.Interior

         . = Pattern xlSolid

         . PatternColorIndex = xlAutomatic

         . ThemeColor = xlThemeColorAccent2

         . TintAndShade = 0.799981688894314

         . PatternTintAndShade = 0

     end With

     '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''

     'Sets the last column to be colored

     Order = Replace (Format (Target, ">"), "V", "") + 8

     'Tests whether the final column is greater than 8

     End If> 8 Then

         'Colours range.

         'In this case the first line is 11 and the last line is the 20, but can be changed according to the need

         For Each C In Range (Cells (11, 9), Cells (20, End))

             C.Select

             with Selection.Interior

                 . = Pattern xlSolid

                 . PatternColorIndex = xlAutomatic

                 . ThemeColor = xlThemeColorAccent1

                 . TintAndShade = 0.399975585192419

                 . PatternTintAndShade = 0

             end With

         Next

     end If

end If

Range ("$ I $ 4"). Select

end Sub

Regards

Thaís

[Former Member]

Hi Thais,

What dimension are there in Column axis??

Please share the screenshots in which you are using the EPMformattingsheet to format the report?

[Former Member]

Hi

The following images:

The columns should be painted as selected version.

example:

V03 version - Match the columns of January, February, March and April.

When the user selects the V03 version, these speakers should come in blue.

If the user selects the V00 version, only to January column should come in blue.

If the user selects the V01 version, only the January and February columns, should come in blue.

If the user selects the V02 version, only the January, February and March columns, should come in blue.

The rest will remain in the original white and orange.

Thank you

[Former Member]

Hi Thais,

You can apply the EPM Formatting for that. See the screenshots that I have attached.

According to the version which user is selecting, write the formula in Row 1. Row 2 (which is in yellow color) having only 0 to 11 numbers for each month you have in your report.

Go to EPMFormattingsheet and apply the conditional formatting under hierarchy level formatting for column header as below snapshot.

Refresh the report and column axis now will get formatted according to Version.

Regards,

Meenu