VBA Code doesn't work when checking Merged Area

[bsher]

Hi all,

I've got a working code, it is the following:

Sub change_title()
Dim i As Integer
i = 2


Dim title_counter As Integer
title_counter = 0


Do


    i = i + 1
    If Cells(i, 7).Interior.ColorIndex = 6 Then title_counter = title_counter + 1


Loop Until Cells(i, 7).Interior.ColorIndex = 6 And Cells(i + 2, 7).Value = ""


MsgBox i
MsgBox title_counter


'Call change_mpa(title_counter)


End Sub

I get the correct i and title_counter variables with this code. However, I did not get the correct values of those variables using the following code:

Sub change_title()
Dim i As Integer
i = 2


Dim title_counter As Integer
title_counter = 0


Do


    i = i + 1
    If Cells(i, 7).Interior.ColorIndex = 6 Then title_counter = title_counter + 1


Loop Until Cells(i, 7).Interior.ColorIndex = 6 And Cells(i + [U][B][COLOR=#ff0000]1[/COLOR][/B][/U], 7).Value = ""


MsgBox i
MsgBox title_counter


'Call change_mpa(title_counter)


End Sub

I've highlighted the difference, the difference is that of the 1.

I'm attaching a screenshot to show you what the page looks like that I'm running it on:

The following link contains more of the screenshot: Screenshot

When I ran the code, it counted the merged cell as blank and basically stopped the loop right there, giving me an i of 11 and a title_counter of 1

So, now that I've thoroughly bored everyone with all the background, here are the questions I've got:

Why does excel not recognize the text throughout the merged cell? Is it because when I merged everything, the text was originally in a different cell?

Is there a way to make excel recognize that the merged cell contains text or is the only way to fix this a workaround like what I made?

Thanks in advance!

 

[Joe4]

Merged cells are notorious for the issues that they cause with things like VBA and sorting, and should really be avoided whenever possible. Most power programmers won't touch them with a ten foot pole!

If you are just merging cells across a single row, instead of using Merged Cells, use the "Center Across Selection" formatting option. You get the same visual effects, without all the issues that merged cells cause.