I have an excel form that has lots of merged cells. I cannot unmerge the cells due to the nature of the form. What I want to do is loop through the merged cells in column A (see image below). If that cell contains data but the adjacent cells in column B or Column C do not then I want to exit sub. Also, if everything is OK and all the cells have data then I want to continue on with different code. I'm having an issue that because the cells are merged, even if the cells all contain data like they are supposed to it is treating it as though they do not have data. Below is the Code and an image from my workbook. I also tried to attach a sample WB but not sure if it worked. I tried adding ".mergearea" to my X variable but that did not work either. I appreciate any help on this.
Sub test1()
Dim sht1 As Worksheet
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Dim x As Range
With sht1
For Each x In Range("test_rng") 'test_rng is named range on worksheet
If x <> "" And x.Offset(0, 16) = "" Or x <> "" And x.Offset(0, 25) = "" Then
MsgBox "Data missing, try again!"
x.Interior.ColorIndex = 19
Exit Sub
Else
x.MergeArea.Interior.ColorIndex = 0
MsgBox "Success!"
End If
Next x
End With
End Sub
Capture.JPG
Bookmarks