Changing background colour on merged Excel cells doesn't work 1

[Halliarse]

I have a project where I am populating and invoice template where the main body of the invoice has alternate lines that have a background colour of white and grey. Depending on the size / length of the invoice text, lines within the invoice are merged and then dependant on the invoice line count being odd / even, coloured accordingly. Unfortunately the code I'm using has no effect on the completed form (I've tried it without the code and it makes no difference), and I cannot see why the code is not actioning on the invoice!

Please can someone advise?

xlInvoiceWorkSheet.Cells.Range("A" & StartingLineCount & ":A" & StartingLineCount + InvoiceDescLineCount - 1).MergeCells = True
                        xlInvoiceWorkSheet.Cells.Range("B" & StartingLineCount & ":B" & StartingLineCount + InvoiceDescLineCount - 1).MergeCells = True
                        xlInvoiceWorkSheet.Cells.Range("E" & StartingLineCount & ":E" & StartingLineCount + InvoiceDescLineCount - 1).MergeCells = True
                        xlInvoiceWorkSheet.Cells.Range("F" & StartingLineCount & ":F" & StartingLineCount + InvoiceDescLineCount - 1).MergeCells = True

                        MergeCount += 1

                        ' Check if MergeCount is Odd / Even and set cell background colour

                        xlInvoiceWorkSheet.Range("A" & StartingLineCount & ":F" & StartingLineCount + InvoiceDescLineCount - 1).Select()
                        xlInvoiceWorkSheet.Cells.Range("F" & StartingLineCount).Activate()

                        If CLng(MergeCount) Mod 2 > 0 Then
                            With xlApp.Selection.Interior
                                .patterncolorindex = Excel.Constants.xlAutomatic
                                .ThemeColor = Excel.XlThemeColor.xlThemeColorDark1
                                .TintAndShade = 0
                                .PatternTintAndShade = 0
                            End With
                        Else
                            With xlApp.Selection.Interior
                                .pattern = Excel.Constants.xlSolid
                                .patterncolorindex = Excel.Constants.xlAutomatic
                                .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3
                                .TintAndShade = 0.799981688894314
                                .PatternTintAndShade = 0
                            End With
                        End If

Many thanks

Steve

 

[SkipVought]

Hi,

Maybe uploading your workbook containing the invoice might help getting to a solution.

BTW, you're working in VBA. Maybe forum707 might be more appropriate.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Halliarse]

Hi, attached is the workbook as requested. If I get no joy, I will also change forums....thanks

 

[SkipVought]

Ok I have your invoice, but under what conditions are you having a problem. It is not evident.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Halliarse]

Skip, I've attached a populated worksheet to assist.

As you can see, the merged rows 22-37 have the coloured background, but these should be transparent as merged rows 15-19, based on my MergeCount being an odd number (3) for that invoice line.

I hope that makes sense.

Cheers

Steve

 

[SkipVought]

According to your code, the variable MergeCount is key to this statement...

'
   If CLng(MergeCount) Mod 2 > 0 Then
'...

Don't know why you're using CLng() since MergeCount ought to be declared as integer. Interger...Long should make no difference.

But if MergeCount gets incrimented by 1 each time the row is shaded, then Mod 2 will alternate between the two shades.

I'd put a break in your code to check the value before the If statement is executed.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Halliarse]

Hi Skip

Yep, I've done that, also validated that it goes through the alternate code correctly and it does, but has no effect on the completed invoice. I removed the CLng()....not sure what my thinking was there!!

 

[strongm]

>BTW, you're working in VBA

Not sure that they are. I present the following a possible evidence:

[tt]MergeCount += 1[/tt]

 

[Halliarse]

Strongm, that line of code does increment correctly, however I've also changed it to MergeCount = MergeCount + 1 with no change to the completed invoice

 

[strongm]

My point was that you were not working in VBA. += is not a VBA assignment operator. It is, however, a VB.NET operator.

 

[strongm]

By the way, is there a reason you are doing it this way, instead of sticking the multiple lines into a single cell and switching on word wrap?

 

[Halliarse]

I did originally have Word Wrap turned on but this gave rise to formatting issues with some of the text! I got round this by applying strict formatting rules to the text and length of each line and that works perfectly. The only issue I have is the colour formatting.

 

[strongm]

>issues with some of the text!

I can't see any difference (as long as we add the padding that merging does). But maybe we don't have the full requirements story yet.

 

[SkipVought]

Give this a try

    ' Check if MergeCount is Odd / Even and set cell background colour

    With xlInvoiceWorkSheet.Range("A" & StartingLineCount & ":F" & StartingLineCount + InvoiceDescLineCount - 1).Interior
'    xlInvoiceWorkSheet.Cells.Range("F" & StartingLineCount).Activate

        If CLng(MergeCount) Mod 2 > 0 Then
            .PatternColorIndex = Excel.Constants.xlAutomatic
            .ThemeColor = Excel.XlThemeColor.xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Else
            .Pattern = Excel.Constants.xlSolid
            .PatternColorIndex = Excel.Constants.xlAutomatic
            .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End If
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Halliarse]

You're genius, thanks Skip!

 

[strongm]

Still think you are overthinking this