Private Sub Worksheet_Activate()

[royca]

I am using the following code on a few different sheets for input from an audit. I don't know if it is the best way to collect input but I like it. What I would like to do is reset the column with the wingdings font "o" every time the user changes sheets. I can not quite seem to figure out the right syntax. How do I make Range("C2:C20") all "o" every time I select the sheet?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:C20")) Is Nothing Then
Cancel = True
With Target
.Font.Name = "Wingdings"
.Font.Size = 20
.HorizontalAlignment = xlCenter
End With
If Target.Value = "o" Then
Target.Value = "þ"
Target.Font.ColorIndex = 10
ElseIf Target.Value = "þ" Then
Target.Value = "ý"
Target.Font.ColorIndex = 3
ElseIf Target.Value = "ý" Then
Target.Value = "o"
Target.Font.ColorIndex = 0
Else
Target.Value = "þ"
Target.Font.ColorIndex = 10
End If
End If

 

[patel45]

put in the workbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set Target = Range("C2:C20")
Cancel = True
With Target
  .Font.Name = "Wingdings"
  .Font.Size = 20
  .HorizontalAlignment = xlCenter
End With
For Each cell In Target
  With cell
    If .Value = "o" Then
      .Value = "þ"
      .Font.ColorIndex = 10
    ElseIf .Value = "þ" Then
      .Value = "ý"
      .Font.ColorIndex = 3
    ElseIf .Value = "ý" Then
      .Value = "o"
      .Font.ColorIndex = 0
    Else
      .Value = "þ"
      .Font.ColorIndex = 10
    End If
  End With
Next
End Sub

 

[p45cal]

Private Sub Worksheet_Activate()
Range("C2:C20") = "o"
end sub

or perhaps:

Private Sub Worksheet_Activate()
With Range("C2:C20")
  .Value = "o"
  .Font.ColorIndex = 0
End With
End Sub