INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Changing background colour on merged Excel cells doesn't work

Changing background colour on merged Excel cells doesn't work

(OP)
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?

CODE --> VB

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

RE: Changing background colour on merged Excel cells doesn't work

Hi,

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

BTW, you're working in VBA. Maybe forum707: VBA Visual Basic for Applications (Microsoft) might be more appropriate.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing background colour on merged Excel cells doesn't work

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing background colour on merged Excel cells doesn't work

(OP)
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

RE: Changing background colour on merged Excel cells doesn't work

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

CODE

'
   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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing background colour on merged Excel cells doesn't work

(OP)
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!!

RE: Changing background colour on merged Excel cells doesn't work

>BTW, you're working in VBA

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

Quote:

MergeCount += 1

RE: Changing background colour on merged Excel cells doesn't work

(OP)
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

RE: Changing background colour on merged Excel cells doesn't work

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

RE: Changing background colour on merged Excel cells doesn't work

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?

RE: Changing background colour on merged Excel cells doesn't work

(OP)
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.

RE: Changing background colour on merged Excel cells doesn't work

Give this a try

CODE

' 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing background colour on merged Excel cells doesn't work

(OP)
You're genius, thanks Skip!

RE: Changing background colour on merged Excel cells doesn't work

Still think you are overthinking this

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close