CODEWITHSUNDEEP
excelvba
pufAmuf
pufAmuf

Reputation: 7795

Using Cell Names in CASE

I have a dynamic Excel spreadsheet where rows are added and removed. For that reason, I can't target cells by using their range. Is it possible to target the cells by using their names in a case scenario?

This is my working code right now:

Select Case Target.Address


Case "$G$14"
        If Range("RequestorField").Value = "" Then
            Range("RequestorField").Interior.Color = RGB(255, 199, 206)
        Else
            Range("RequestorField").Interior.Color = RGB(215, 228, 188)
        End If

Replacing $G$14 with RequestorField kills the code.

Any ideas? Thanks!

ps: This all goes in the WORKSHEET_CHANGE function, hence the reason why I need to use "case".

Upvotes: 0

Views: 260

Answers (1)

Alex P
Alex P

Reputation: 12489

Use Target.Name.Name

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Name.Name

    Case "RequestorField"
        If Range("RequestorField").Value = "" Then
            Range("RequestorField").Interior.Color = RGB(255, 199, 206)
        Else
            Range("RequestorField").Interior.Color = RGB(215, 228, 188)
        End If 
    End Select

End Sub

Upvotes: 3

Related Questions