Target.Value help in Worksheet_Change

[ProspectiveCounselor]

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.

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

[patel]

what's the goal of

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

[Admin]

Hi

add this line above the Select case statement

If Target.Cells.Count > 1 Then Exit Sub

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

[ProspectiveCounselor]

what's the goal of

        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.

Hi

add this line above the Select case statement

If Target.Cells.Count > 1 Then Exit Sub

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

Thank you, that worked.