Excel VBA Method 'ClearContents' of Object Range Failed
<p>I've looked at the similar questions about "Method of Object Range Failed" but none seem to help me solve my problem.</p>

<p>The error I receive is: "Method 'ClearContents' of Object Range Failed"</p>

<p>Essentially what I'm trying to achieve is that if cell value of BG21 = 1 And the length of the string in adjacent cell (BH21) is 0 Then timestamp the adjacent cell (BH21). Ideally I want to repeat this for Range (BG21:BG35). I know I could use a for each method but I want to get this error cleared first.</p>

<p>I don't want to use the cell formula because I need a timestamp the moment the cell BG21 changes to 1. If I use a cell formula then anytime the workbook is opened the timestamp changes. </p>

<p>The code I have is:</p>

<pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
   Dim wb As Workbook, ws As Worksheet

   Set wb = ThisWorkbook
   Set ws = wb.Worksheets("Submittal Kickoff Meeting")

    If (ws.Range("$BG$21").Value = 1 And len(ws.Range("$BH$21").Value) = 0 And Target.Address = ws.Range("$BG$21")) Then
        ws.Range("$BH$21").Value = Format(Now(), "m/dd/yyyy hh:mm:ss AM/PM")
    Else
        ws.Range("$BH$21").ClearContents  '&lt;--- Error occurs here
    End If
End Sub
</code></pre>

<p>The error occurs on line:</p>

<pre><code>ws.Range("$BH$21").ClearContents
</code></pre>

<p>I have also tried:</p>

<pre><code>ws.Range("$BH$21").Value = ""
</code></pre>

<p>But I received the same error code</p>

<p>Any advice??</p>

<p>.</p>