Posts by PrasadK

i have applied code in excel 2007 version

I am using excel 2007

why i have added on error resume next

When i have applied this code into worksheet change and i went to sheet and enter a mobile number in a cell

Then i get run time error

Thank you so much royUK

it's working perfectly

i have applied this code like this it's working thank you so much

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
 Const s As String = "+91-"
 If Len(Target) = 10 Then Target.Value = s & Target.Value
End Sub

Can you change this code into worksheet change event

i have applied this macro to add +91- before mobile number of 10 digits

here can any one change this code into worksheet event change macro because

when i type mobile number in a cell +91- should be add automatically

here i know we can add +91- in cell range by changing cell range formating into format cells

in format cells we have apply custom format to add +91- before mobile number

here i want to add +91- before mobile number in general format only

Sub Add()
For Each r In Range("A1:A4")
    If Not (Left(r.Value, 2) = "+91-") Then
        r.Value = "+91-" & r.Value
    End If
Next r
End Sub

Thank you so much rollis13

i have done all correctly it's not unprotecting

when i am run this macro it's protecting whole active sheet and again run again macro it's don't unprotecting i want to protect and unprotect just a in range A1 to D20

Sub lockcells()
 Dim Rng As Range
 
 On Error GoTo err_handler
 Set Rng = Range("A1:D20").SpecialCells(xlCellTypeConstants)
 ActiveSheet.Unprotect Password:="123"
 Rng.Cells.Locked = True
 Rng.FormulaHidden = False
err_handler:
 ActiveSheet.Protect Password:="123", UserInterFaceOnly:=True
End Sub

any one answer this

when i am running this macro to protect and unprotect in given range it is protecting as well and when i am again running this macro to unprotect it's not unprotecting

Sub lockcells()
    Dim Rng
    Dim MyCell
    Set Rng = Range("A1:D20")
    For Each MyCell In Rng
        If MyCell.Value = "" Then

        Else: ActiveSheet.UnProtect Password:="123"
            MyCell.Locked = True
            MyCell.FormulaHidden = False
            ActiveSheet.Protect Password:="123", UserInterFaceOnly:=True
        End If
    Next
End Sub

I need a macro on worksheet change event to apply cell borders automatically based on cell value



Like if I type any value in cell then apply border automatically

Thank you it's working perfectly

and one more help if cell have 3 or 4 mobile number how to extract with this formula

Hai i am new to OzGrid Forum

How to Extract 10 Mobile Numbers to Separate Columns with any Single Formula