CODEWITHSUNDEEP
excelvba
user3790788
user3790788

Reputation: 53

VBA Referencing named cells

I have a table where some cells are grayed out. I want to write a code that will automatically turn a cell white when clicked on, and stay that way if something is typed in. If nothing is typed in, it will return back to gray. Currently I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

            Dim Temp As Range                
            If Range(“Temp”).Value = "" Then
            Range("Temp").Interior.Pattern = xlGray25


            'If the cell is gray, it turns it white when clicked on. This part works fine
            If Target.Interior.Pattern = xlGray25 Then
            Target.Interior.Pattern = xlSolid
            ‘Labels the changed cell as “Temp” so it can be referenced
            ThisWorkbook.Names.Add “Temp”, Target

 End If
End Sub

However, the 

If Range("Temp").Value = "" Then

line gets a mismatch error. I tried it without the .value and setting "Temp" to a specific cell, but neither works. All the other sources I checked said that should be the correct way to reference a named cell. Any help would be greatly appreciated.

Upvotes: 1

Views: 1142

Answers (2)

Robert Co
Robert Co

Reputation: 1715

There is no need for VBA. Please read about "Conditional Formatting". It's in the Style group at the middle of the Home ribbon.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

I would suggest you use a module-level variable to keep track of the previous selection.

Private mrPrevious As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not mrPrevious Is Nothing Then
        'If IsEmpty(mrPrevious.Value) Then
        If IsEmpty(mrPrevious.Cells(1).Value) Then
            mrPrevious.Interior.Pattern = xlGray25
        End If
    End If

    If Target.Interior.Pattern = xlGray25 Then
        Target.Interior.Pattern = xlSolid
    End If

    Set mrPrevious = Target

End Sub

Upvotes: 2

Related Questions