Target.Value help in Worksheet_Change

Hello,

I'm having an issue with some code in my Worksheet_Change event. Whenever I select several cells and fill down or right, or whenever I delete the contents of merged cells (which the macro unmerges automatically), I get a debug error because of a type mismatch. I would assume Target.Value is a variant (as it could be a string or number) and therefore can accept any data type, but I'm guessing it's not one because I keep getting the errors.

Here is an example of my code. If you guys could tell me how to stop getting the type mismatch error, I would be very thankful.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.value
        Case "R", "r"
            Target.Interior.Color = RGB(255, 0, 0)  'background is red
            Target.Font.Color = RGB(0, 255, 255)    'font color is cyan
        Case "G", "g"
            Target.Interior.Color = RGB(0, 255, 0)  'background is green
            Target.Font.Color = RGB(255, 0, 255)    'font color is magenta
        Case "B", "b"
            Target.Interior.Color = RGB(0, 0, 255)  'background is blue
            Target.Font.Color = RGB(255, 255, 0)    'font color is yellow
            Range(Target.Address, Cells(Target.row + 1, Target.Column + 1)).Merge
        Case "" '
            Target.Interior.ColorIndex = 0
            Target.Font.Color = RGB(0, 0, 0)
            Range(Target.Address).UnMerge
    End Select
End Sub

---

what's the goal of

Code:

---

        Case "" '
            Target.Interior.ColorIndex = 0
            Target.Font.Color = RGB(0, 0, 0)
            Range(Target.Address).UnMerge

---

Hi

add this line above the Select case statement

Code:

---

If Target.Cells.Count > 1 Then Exit Sub

---

this will allow to exit the sub while working on multiple cells.

Quote:

---

Originally Posted by patel

what's the goal of

Code:

---

        Case "" '
            Target.Interior.ColorIndex = 0
            Target.Font.Color = RGB(0, 0, 0)
            Range(Target.Address).UnMerge

---

---

Well, Case "" represents the deletion of cell contents, so it's supposed to set the background to no color and the font color to black, and then it's supposed to unmerge the cells. The background and font colors are changing only on the already unmerged cells, though.

Quote:

---

Originally Posted by Admin

Hi

add this line above the Select case statement

Code:

---

If Target.Cells.Count > 1 Then Exit Sub

---

this will allow to exit the sub while working on multiple cells.

---

Thank you, that worked.