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.