Updating cell value by case

[twlove]

I want the value in cell H18 to appear or not appear based on the
value selected and displayed in cell N14. For example, when cell N14
= "Diamond" I want the value in cell H18 to be hidden. Here's my code
but the statement for case "Diamond" won't execute:

Private Sub Worksheet()

Dim Pattern As String

Pattern = Range("N14")

Application.ScreenUpdating = False
Application.ActiveSheet.Range("H18").Select
Application.ScreenUpdating = True

Select Case (Pattern)

'Application.ActiveSheet.Range("H18").Select

Case "Diamond"

Cell.Value = ""

End Select


End Sub

 

[Dave Peterson]

First, I wouldn't name a macro Worksheet or use a variable named Pattern. Both
of these are used by excel's VBA.

Second, maybe something like:

Option Explicit
Private Sub myMacro()

Dim myPattern As String
Dim myCell As Range

myPattern = Range("N14").Value

Set myCell = Range("h18")

Select Case LCase(myPattern)
Case LCase("Diamond")
myCell.Value = ""
End Select

End Sub

 

[Guest]

twlove,

If you have a real need to select H18, then, change

Cell.Value = ""

with this

ActiveCell.Value = ""


However, note that you don't need to select a cell to work with it so you
can also code it this way:

Private Sub Worksheet()

Dim Pattern As String

Pattern = Range("N14")

Select Case (Pattern)
Case "Diamond"
Range("H18").Value = ""
End Select

End Sub