VBA formating

[K-R-Hattie]

Hi

I am trying to format a cell using VBA, I want to enter in cell C3 "Running" and the cell to return the value 1, is this something any one can help me with?

thanks

 

[Simon Lloyd]

I can't see what use it would be as it would be easier to just type 1 instead, anyway this goes in the worksheet code module (right click the tab of the sheet you are working with and choose "View code")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$3" Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Value = LCase("Running") Then
Target.Value = 1
End If
End Sub

 

[K-R-Hattie]

Hi many thanks, I haven’t explained myself, I want the value to be 1 but the word running to still show in the cell.

 

[Weaver]

Select the cell and choose cell formatting

In the wizard, pick the 'number' tab

Select 'custom' and in the 'type' box, enter the following

"Running"

Include the quotes

If you only want it to say "running" for values of 1, try:

[=1]"Running";General

 

[Simon Lloyd]

Sorry you've completely lost me now!

 

[Simon Lloyd]

Select the cell and choose cell formatting

In the wizard, pick the 'number' tab

Select 'custom' and in the 'type' box, enter the following

"Running"

Include the quotes

Won't that show "Running" for any value entered?

 

[Weaver]

Won't that show "Running" for any value entered?

Sorry, see my edit

 

[Weaver]

In VB

Sub setformat()
    Range("C3").NumberFormat = "[=1]""Running"";General"
End Sub

 

[Ruddles]

I am trying to format a cell using VBA, I want to enter in cell C3 "Running" and the cell to return the value 1, is this something any one can help me with?

You have a squillion cells to play with in a worksheet - why do you want to shoehorn two things into a single cell? Why not just use two separate cells?

 

[K-R-Hattie]

Thanks for all the input; Weaver your last post did the trick.