CODEWITHSUNDEEP
excelmergevba
C. Cons
C. Cons

Reputation: 57

Code Error for Merged Cells

I am new to this and need help resolving this problem. I am trying to use this code on excel to change a cell's color and text, but it will not work for merged cells. How do I make it work for merged cells?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("$C$17:$C$80")) Is Nothing Then Exit Sub
    Select Case Target
    Case ""
        Target = "Priority 1"
        Target.Interior.ColorIndex = 3
    Case "Priority 1"
        Target = "Priority 2"
        Target.Interior.ColorIndex = 6
    Case "Priority 2"
        Target = "Priority 3"
        Target.Interior.ColorIndex = 45
    Case Else
        Target = ""
        Target.Interior.ColorIndex = 15
    End Select
    Cancel = True
End Sub

Upvotes: 2

Views: 687

Answers (1)

Automate This
Automate This

Reputation: 31364

Super close, just change one line: Select Case Target to Select Case Target.Item(1)

Full Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("$C$17:$C$80")) Is Nothing Then Exit Sub

    Select Case Target.Item(1)
    Case ""
        Target = "Priority 1"
        Target.Interior.ColorIndex = 3
    Case "Priority 1"
        Target = "Priority 2"
        Target.Interior.ColorIndex = 6
    Case "Priority 2"
        Target = "Priority 3"
        Target.Interior.ColorIndex = 45
    Case Else
        Target = ""
        Target.Interior.ColorIndex = 15
    End Select
    Cancel = True
End Sub

Results:

You can see here that I have a merged area from C23 to C26

enter image description here

Upvotes: 2

Related Questions