Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Hans (or anyone), a portion of a macro that you helped me with is below:

    strColumn = "C"
    With ActiveSheet
    lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
    For lngRow = 2 To lngLastRow
    If IsDate(.Cells(lngRow, strColumn).Value) And CDate(.Cells(lngRow, strColumn).Value) < Date - 30 Then
    .Rows(lngRow).Interior.ColorIndex = 38
    End If
    Next lngRow
    End With

    Column C is supposed to be a date in the format mm/dd/yy, but I now have been handed some txt files which get pulled into Excel and column C sometimes is either BLANK or contains: / / (only the slashes).

    As a result, this portion has an error.

    What should I insert to resolve these two issues? Actually, if the date is blank or just slashes, I'd like to use the ColorIndex with a different color.

    Thanks in advance.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='kweaver' post='776296' date='21-May-2009 14:13']Hans (or anyone), a portion of a macro that you helped me with is below:

    strColumn = "C"
    With ActiveSheet
    lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
    For lngRow = 2 To lngLastRow
    If IsDate(.Cells(lngRow, strColumn).Value) And CDate(.Cells(lngRow, strColumn).Value) < Date - 30 Then
    .Rows(lngRow).Interior.ColorIndex = 38
    End If
    Next lngRow
    End With

    Column C is supposed to be a date in the format mm/dd/yy, but I now have been handed some txt files which get pulled into Excel and column C sometimes is either BLANK or contains: / / (only the slashes).

    As a result, this portion has an error.

    What should I insert to resolve these two issues? Actually, if the date is blank or just slashes, I'd like to use the ColorIndex with a different color.

    Thanks in advance.[/quote]
    Try this:
    Code:
    	strColumn = "C"
    	   With ActiveSheet
    		   lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
    		   For lngRow = 2 To lngLastRow
    			   If IsDate(.Cells(lngRow, strColumn).Value) Then
    				   If CDate(.Cells(lngRow, strColumn).Value) < Date - 30 Then
    					   .Rows(lngRow).Interior.ColorIndex = 38
    				   End If
    			   Else
    				   .Rows(lngRow).Interior.ColorIndex = 3
    			   End If
    		   Next lngRow
    	   End With
    Code revised to provide new colour for non date cells.
    Regards
    Don

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks. That did the trick.

    [quote name='wdwells' post='776329' date='21-May-2009 18:01']Try this:
    Code:
    	strColumn = "C"
    	   With ActiveSheet
    		   lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
    		   For lngRow = 2 To lngLastRow
    			   If IsDate(.Cells(lngRow, strColumn).Value) Then
    				   If CDate(.Cells(lngRow, strColumn).Value) < Date - 30 Then
    					   .Rows(lngRow).Interior.ColorIndex = 38
    				   End If
    			   Else
    				   .Rows(lngRow).Interior.ColorIndex = 3
    			   End If
    		   Next lngRow
    	   End With
    Code revised to provide new colour for non date cells.[/quote]

Posting Permissions

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