Change "Yes" to a tick

[SaraWitch]

Hello peeps,

I would like to change Yes to a tick in the same cell - can this be done? I know how to acheive this in a different column with a formula, but would like it to automatically change in the same cell, same column. Conditionally formatting doesn't work, unfortunately, so I'm guessing a VBA?

Ta muchly, folks!

 

[Fluff]

How is the Yes getting into the cell?

 

[Saurabhj]

Hi SaraWitch,

Use Worksheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
    If LCase(Target.Value) = "yes" Then
        Target.Value = ChrW(&H2713)
    End If
End Sub

 

[SaraWitch]

Sorry, @Fluff, by Data Validation List, or manual entry.

But @Saurabhj vba works a treat - could I add to this to change to a cross if "no"?

 

[Fluff]

Which cells do you want this to work on?

 

[SaraWitch]

Which cells do you want this to work on?

In one column, e.g., G32:G51. So if "yes" is selected from DVL in cell G32, then tick, if no, then cross. @Saurabhj solution works a treat for ticks... I'm Googling the ChrW code for a cross, but can't find it yet!

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("G32:G51")) Is Nothing Then
      Select Case LCase(Target.Value)
         Case "yes": Target.Value = ChrW(10004)
         Case "no": Target.Value = ChrW(10006)
      End Select
   End If
End Sub

 

[SaraWitch]

Perfect; thank you! I fiddled with a different type of tick and cross, but this is great.

Thank you both for your help...

 

[Fluff]

Glad we could help & thanks for the feedback.