Word change to number automaticaly

[dave2017]

Hi Guys,

I am trying to make data in a cell change into a number !


For example if the following words

Off ,Holiday , Rotation or Travel

are typed or pasted into cell G4 it will automatically change to the following number 0:00

I hope you can help with this question.

Thanks Dave

 

[pedie]

try something like this!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range




    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Dim x As Long
    Dim y As Variant
    
    Set KeyCells = Range("G4")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
            
            x = Target.Row
            y = Target.Value
            
            Application.EnableEvents = False
                If y = "Off" Or _
                        y = "Holiday" Or _
                        y = "Rotation" Or _
                        y = "Travel" Then
                            Target.Value = "0:00"
                            
                End If
            Application.EnableEvents = True
            
    End If
End Sub

 

[dave2017]

Hi Pedie,

Thanks for your reply, I am new to excel ! would it be possible for you to direct me on how to implement your theory.

Cheers Dave

 

[pedie]

from your vbe editor/project double click on your sheet name module (class) and place below code. this code should run every time value is changed in G4.