Check cell format is Date ignore blank cells

MartMartnz · Nov 20, 2020

Hello,

I am not an expert in VBA and I would appreciate some help with a problem I am trying to solve.

I have a column (B2:B14) where the users need to insert the delivery dates of various product, I would like to insert a VBA code to automatically check if the information inserted in column B has the Date format (dd-mm-yyyy), if the format is not correct the cell's fill colour change to red.

I adapted a code from the net, but the result is not the ideal, the blank cells also change the background to red.

HTML:

Private Sub DateFormat()
Dim myRange As RangeSet myRange = Range("B2:B14")

myRange.FormatConditions.Delete
myRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _        Formula1:="01-01-2020", Formula2:="31-12-2040"myRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Is possible to change the code in order to ignore the blank cells?

Any suggestions would be greatly appreciated

Thank you in advanced.

RET · Nov 29, 2020

Try this

HTML:

Private Sub DateFormat()
   Dim myRange As Range
   Dim Celda As Range
   
   Set myRange = Range("B2:B14")
   myRange.FormatConditions.Delete
   For Each Celda In myRange
      If Celda.Value <> "" Then
         Celda.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:="01-01-2020", Formula2:="31-12-2040"
         Celda.FormatConditions(1).Interior.Color = RGB(255, 0, 0)    
      End If
   Next Celda
End Sub

p45cal · Nov 30, 2020

try:

Click on the picture to get a clearer view.

Oops, fotrgot the code:

Code:

Private Sub DateFormat()
Dim myRange As Range
Set myRange = Range("B2:B14")
With myRange
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:="01-01-2020", Formula2:="31-12-2040"
  .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
  .FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(B2)"
  .FormatConditions(2).SetFirstPriority
End With
End Sub

Check cell format is Date ignore blank cells

MartMartnz

New member

RET

New member

p45cal

Super Moderator

Attachments