Unable to change the color in excel column after generating report from n printing on demand report.

[deep2021]

Hi All,

We are using n printing ODAG. After generating the excel report I am unable to change the color for cells in the one of the columns.

PFB screenshot, I am unable to change color for column B

Can you please suggest what settings do I need to do to enable color changing for the same.

[Lech_Miszkiewicz · Accepted solution]

Hi @deep2021 

First - clarification in regards to naming.

• "ODAG" is Qlik Sense feature called "OnDemand App Generation" - it is not supported with Qlik NPrinting
• Since you are talking about NPrinting I am assuming you are talking about NPrinting report generated from Qlik Sense front-end using "OnDemand reporting" extension button - just confirm that this is true in your case

Second - Have you designed/build this report? if not talk to the person who developped it. I guess this is configured in NPrinting designer in the report template.

Third - If the report is generated make sure you are in edit mode (not read only mode of excel), also purely from excel point of view - do you have Excel conditional formatting in this excel file?

I guess this is the reason why  you cannot change the setting as conditional formatting takes precedence over manual formatting. that is working as desinged in excel and is purely excel functionality (not NPrinting)!

cheers

Lech

[deep2021]

Hi Lech,

Yes, I am talking aboutN printing and reports are generated from qliksense front end using on demand button.

For number formatting macros are enabled in n printing excel template. But the problem is related to the excel reports which are generated from n printing. Then how come problem is related to excel not related to n printing template.

Can you please suggest where do i need to change the settings.

At the end of the day, end users can change color as per their choice in the generated excel report.

Thanks 

[deep2021]

Hi Lech,

 In the final generated Excel  report there are rules but in n printing excel template there are no rules. I am not getting how the rules are generating in the final excel report.

Thanks

[Lech_Miszkiewicz]

Hi,

all what I said is that the conditional formatting is an excel functionality. I understand that you may set it up in NPrinting Designer, but the way it works is purely based on excel. Same applies to use of macros.

Without seeing your template we will not be able to suggest anything more. On top of it if you say that you are having macros in the template, maybe you are executing them and maybe macros are creating some conditional rules.

Long story short - if the output has conditional formatting, there needs to be something putting it there and it is not NPrinting. It either exists in the template or gets created via macro. 

Suggestions to troubleshoot:

• remove macros and test
• make sure you check entire template for conditional formatting
  • double check if there are any hidden sheets in excel and make sure that you check all range when searching conditional formats

I dont have anything else I can think of.

[deep2021]

Hi,

Can you please suggest what kind of macro do i need to use.

Below are the rules in the excel,

Could you please help me out.

I am trying out the below macro but it is not working as excepted.

Private Sub Workbook_Open1()

    Dim ws As Worksheet, rng As Range

    Dim mySel As Range

    For Each ws In ActiveWorkbook.Worksheets

        Set mySel = ws.Range("C9:O47")

        For Each rng In mySel

             If ws.Name <> "INDEX" Then

                Debug.Print ws.Name

                    If rng.HasFormula Then

                        rng.Formula = rng.Value

                    End If

                With rng

                  .Font.FontStyle = .DisplayFormat.Font.FontStyle

                  .Interior.Color = .DisplayFormat.Interior.Color

                  .Font.Strikethrough = .DisplayFormat.Font.Strikethrough

                End With

    mySel.FormatConditions.Delete

             End If

    Next rng

    Next ws

End Sub

Thanks,

[Lech_Miszkiewicz]

Why do you have this conditional formatting in your template? If you dont use it for anything just edit template and delete it. Make sure you select "workbook" to see all conditional formats you have on the sheet.

If you need conditional formatting for number formats they can still be there as long as you dont touch "Fill" options when configuring it

 Why do you try to use macro to delete conditional formatting?

If you still have issues just DM me and I may be able to jump on quick conf-call as this is either very simple and will take 2 seconds to fix or very complex and we just dont have enough info on what you are trying to achieve. 

cheers

[deep2021]

Hi Lech,

Thanks for your valuable repose.  I already tried after removing all above rules but it seems that the number formatting does not work as per the expectation. 

There is need to set formatting based on all above 3 conditions. Could you please suggest what and  where do I need to make the changes.

Thanks

[Lech_Miszkiewicz]

I understand that you may need to have conditional formats for numbers, but you dont need to setup background colour in it: See gif below (maybe open it in new window to see clearly settings):

Otherwise like i said - DM me and we can schedule call - I am Sydney based so keep in mind time zone.

cheers