Format cell to display "Y" or "N"when entering a 1 or zero

[Guest]

How can I format a cell to display a "Y" or "N" when a value of 1 or 0 is
entered?

 

[Don Guillett]

You can't. But right click sheet tab>view code>insert this.
Now if you are in the specified range you will get what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a2:a22")) Is Nothing Then
If Target = 1 Then Target = "Y"
If Target = 0 Then Target = "N"
End If
End Sub

 

[Guest]

CUSTOM format cell as "Y";;"N"

HTH

 

[Guest]

Let me clarify further. How do I prevent someone from entering anything
other than a 1 or 0?

In other words, I want a 1 entered so the cell displays a "Y" or I want a 0
entered so the cell displays a "N". All other options should be invalid.

 

[Don Guillett]

I just learned a new one but it does Y for anything above 1 also.

 

[Guest]

You could use Data Validation with list of 0,1 plus formatting as per
previous reply.

 

[Guest]

That works. Thanks!

You could use Data Validation with list of 0,1 plus formatting as per
previous reply.

 

[Gord Dibben]

Data>Validation>Allow>Whole Number

Minimum.....0

Maximum.....1

The do the formatting as Toppers suggests.


Gord Dibben MS Excel MVP