Format only blank cells in the range

[khalid79m]

Dim cell As String
With Range("Talk_Time")
    cell = .Cells(1, 1).Address(False, False)
    .Locked = False
    .NumberFormat = "hh:mm:ss"
    .Validation.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, _
    Formula1:="=AND(" & cell & ">=TIME(0,0,1)," & cell & "<=TIME(23,59,59))"
    .Validation.IgnoreBlank = False
    .Validation.ErrorTitle = "Time Error"
    .Validation.ErrorMessage = "Please Enter Hours, Minutes & Seconds in this format 01:20:00"
    .Validation.ShowError = True
    .WrapText = False
    .ColumnWidth = 12
    .Interior.ColorIndex = 43
    .EntireColumn.Hidden = False
End With

my code, here works but only one problem I only want it to

 
.NumberFormat = "hh:mm:ss"

blank cells within range talktime.

how can i do this ?

[Bob Phillips]

Untested

Dim cell As String
Dim rng As Range
With Range("Talk_Time")
    cell = .Cells(1, 1).Address(False, False)
    .Locked = False
    On Error Resume Next
    Set rng = .SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rng Is nothing Then rng.NumberFormat = "hh:mm:ss"
    .Validation.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, _
    Formula1:="=AND(" & cell & ">=TIME(0,0,1)," & cell & "<=TIME(23,59,59))"
    .Validation.IgnoreBlank = False
    .Validation.ErrorTitle = "Time Error"
    .Validation.ErrorMessage = "Please Enter Hours, Minutes & Seconds in this format 01:20:00"
    .Validation.ShowError = True
    .WrapText = False
    .ColumnWidth = 12
    .Interior.ColorIndex = 43
    .EntireColumn.Hidden = False
End With

[khalid79m]

Thanks will test it and get back to you

[khalid79m]

works perfect thanks

[Gossioii3]

A wonderful article…. In my life, I have never seen a man be so selfless in helping others around him to get along and get working.

[khalid79m]

Don't talk in riddles spit it out