Results 1 to 3 of 3

Thread: Check cell format is Date ignore blank cells

  1. #1
    Neophyte MartMartnz's Avatar
    Join Date
    Nov 2018
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Check cell format is Date ignore blank cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Code:
    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.

  2. #2
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    10
    Articles
    0
    Excel Version
    2019
    Try this

    HTML Code:
    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

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,844
    Articles
    0
    Excel Version
    365
    try:
    Click image for larger version. 

Name:	2020-11-30_135850.png 
Views:	7 
Size:	12.5 KB 
ID:	10217
    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	2020-11-30_135850.png 
Views:	3 
Size:	12.5 KB 
ID:	10216  
    Last edited by p45cal; 2020-11-30 at 03:09 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •