PDA

View Full Version : [SOLVED] Format only blank cells in the range



khalid79m
03-19-2009, 09:31 AM
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
03-19-2009, 09:50 AM
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
03-24-2009, 05:02 AM
Thanks will test it and get back to you

khalid79m
04-16-2009, 03:26 AM
works perfect thanks

Gossioii3
04-17-2009, 01:24 PM
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
04-17-2009, 02:57 PM
Don't talk in riddles spit it out