Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 41

Thread: VBA Code Add Condition
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Quote Originally Posted by Fluff View Post
    Let's stick to one problem at a time. You have not changed the if statements as I showed.
    Also NEVER use "On Error Resume Next" in that manner, as it will simply hide problems.
    Well I did change as you said so
    but getting debug msg. Compile error ... End with without with
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  2. #22
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,041
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code Add Condition

    Can you post that code?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #23
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Quote Originally Posted by Fluff View Post
    Can you post that code?
    Sure, why not

    Code:
    Sub formatdates()
    
    Dim rngCell As Range
    Dim lngLstRow As Long
    Dim strCol(1 To 9) As String
    
    
    strCol(1) = "N"
    strCol(2) = "P"
    strCol(3) = "R"
    strCol(4) = "T"
    strCol(5) = "V"
    strCol(6) = "X"
    strCol(7) = "Z"
    strCol(8) = "AB"
    strCol(9) = "AD"
    
    
    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    
    
       For I = 1 To 9
           For Each rngCell In Range(strCol(I) & "4:" & strCol(I) & lngLstRow)
             With rngCell
             
          If .Value <> "" Then
                
        If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = Date - .Value & " Days"
                
        ElseIf .Offset(, 1).Value = "" And .Value < Date And Date - .Value = 1 And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = Date - .Value & " Day"
                
        ElseIf .Offset(, 1).Value = "" And .Value = Date And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = "Today"
                
        
         
                Else
                   .Resize(, 2).Font.ColorIndex = 1
                End If
             
             If .Value = "Not Applicable" Then
             .Resize(, 2).Font.ColorIndex = 1
             
             End If
             End With
          Next rngCell
       Next I
    End Sub
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  4. #24
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,041
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code Add Condition

    How about
    Code:
       For i = 1 To 9
          For Each rngCell In Range(strCol(i) & "4:" & strCol(i) & lngLstRow)
             With rngCell
                If .Value <> "" Then
                   If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 Then
                      .Resize(, 2).Font.ColorIndex = 3
                      .Offset(, 1).Value = Date - .Value & " Days"
                   ElseIf .Offset(, 1).Value = "" And .Value < Date And Date - .Value = 1 Then
                      .Resize(, 2).Font.ColorIndex = 3
                      .Offset(, 1).Value = Date - .Value & " Day"
                   ElseIf .Offset(, 1).Value = "" And .Value = Date Then
                      .Resize(, 2).Font.ColorIndex = 3
                      .Offset(, 1).Value = "Today"
                   Else
                      .Resize(, 2).Font.ColorIndex = 1
                   End If
                   If .Value = "Not Applicable" Then
                      .Resize(, 2).Font.ColorIndex = 1
                   End If
                End If
             End With
          Next rngCell
       Next i
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #25
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Not working friend.

    Run time error 13 type mismatch

    This part of the code highlighted

    Code:
    If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 Then
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  6. #26
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,041
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code Add Condition

    What is the value of rngCell & the cell to the right when you get the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #27
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Quote Originally Posted by Fluff View Post
    What is the value of rngCell & the cell to the right when you get the error?

    Both rngCell & the cell to the right can have below possible values

    1) Blank Cell ------------> But if you apply isblank formula to a cell having no values will result FALSE... It wont return true
    2) A Date
    3) text "Not Applicable"

    Please note that all above possible values are copied from some other sheet & they are results of a formula and then pasted here as paste special values.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  8. #28
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,041
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code Add Condition

    What are the EXACT values when you get the error?
    Also for the date is you use =ISNUMBER(C2) where C2 is one of the dates, what does it return.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #29
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Quote Originally Posted by Fluff View Post
    What are the EXACT values when you get the error?
    Also for the date is you use =ISNUMBER(C2) where C2 is one of the dates, what does it return.
    the error value I get is #N/A & to control this I use iferror function to not to get errors

    For example this formula gives me an error #N/A
    Code:
    =IF(AN726="Not Applicable","Not Applicable",INDEX(fri_dpi_labtest_date,MATCH(1,($A726=fri_dpi_labtest_po)*("FRI 3"=fri_dpi_labtest_category),0)))
    So to control this I applied iferror function
    Code:
    =IFERROR(IF(AN726="Not Applicable","Not Applicable",INDEX(fri_dpi_labtest_date,MATCH(1,($A726=fri_dpi_labtest_po)*("FRI 3"=fri_dpi_labtest_category),0))),"")
    And where there is a date if I use =isnumber() I get TRUE as a result
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  10. #30
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,041
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code Add Condition

    Please answer my first question
    Last edited by Fluff; Jul 16th, 2019 at 10:04 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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