Require help in the code

[kaushik03]

Hi all,


I have an annoying problem here.


What I am doing here is, based on criteria I am coloring the rows.


Criteria1:

Fill rows with green if due date = today (Col F contains due date) and status <> pending scoping call (Col G contains project status)


Criteria2:

Fill rows with black and color fonts with red if due date<today and status <> pending scoping call


I have constructed IF statement and run the same under FOR loop. The macro is working fine for second criteria but something is happening wrong for first criteria. I do not understand why code is not working for the first criteria.


For your reference, a first criterion meets at row 11.


Please see module1 for the code.


http://speedy.sh/U4rTd/ClorCode.xlsm


I am not able to understand where I am doing the mistake.


Looking forward your help to correct me.


Regards,

Kaushik

 

[NARAYANK991]

Hi Kaushik ,


Try this :

[pre]

Sub MMCRO()
Dim tempdate As Date
tempdate = Date

Dim lastrow As Long
lastrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
'   Loop to fill rows with green if due date = today and status <> pending scoping call

For Cntr = 2 To lastrow
If ActiveSheet.Range("G" & (Cntr)).Value <> "Pending scoping call" Then
If Int(ActiveSheet.Range("F" & (Cntr)).Value) = tempdate Then
ActiveSheet.Range("A" & Cntr & ":G" & Cntr).Interior.ColorIndex = 14
ElseIf Int(ActiveSheet.Range("F" & (Cntr)).Value) < tempdate Then
With ActiveSheet.Range("A" & Cntr & ":G" & Cntr)
.Interior.ColorIndex = 1
.Font.ColorIndex = 3
End With
End If
End If
Next
End Sub

[/pre]
Narayan

 

[Hui]

Kaushik

Your date column, Column F has dates as 7/02/2013 8:00:00 PM

Temp date is 7/02/2013, without the times


Change the first If line from

If ActiveSheet.Range("F" & Cntr).Value = tempdate And ActiveSheet.Range("G" & (Cntr)).Value <> "Pending scoping call" Then

to:

If Left(ActiveSheet.Range("F" & Cntr).Value, InStr(1, ActiveSheet.Range("F" & Cntr).Value, " ")) = tempdate And ActiveSheet.Range("G" & (Cntr)).Value <> "Pending scoping call" Then

The < in the second If is more forgiving

 

[kaushik03]

Hi Narayan,


It's always been a pleasure for me to have the solution from you.


However, may I request you to please explain me the problem involved in the earlier code?


Thanks once again for your help.


Regards,

Kaushik

 

[kaushik03]

Hui,


Thank you for your guidance.


I noticed that earlier. But I was not able to understand why the code was not working for the first criteria while the same (only difference is the date value in the column is < today) was working for the other criteria (while both the cases the values in Col F are coming as date and time together).


If you could explain this, that would would be great.


However, thank you very much for showing the trick to tackle this.


Regards,

Kaushik

 

[Hui]

Your date column, Column F has dates as 7/02/2013 8:00:00 PM

Temp date is 7/02/2013, without the times

Hence the two aren't equal

 

[Hui]

The Dates/Times you are using are text, Not Date/Time Values


In the second Loop

The date&time is less than today as that is true even for a Text Comparisson


Select the line with the First If and Press F9 (that adds a breakpoint)

Run the code and when it stops at the If line

In the intermediate pane type


? Range("F" & Cntr).Value

Excel will display 07/02/2012 08:00 am


then type


? tempdate

Excel will display 07/02/2012

 

[NARAYANK991]

Hi Kaushik ,


I think Hui's explanation says it all ; the values in column F are a combination of Date and Time , whereas when you assign the system value of Date to tempdate , you are assigning only the date part.


In Excel , when you enter a date such as 2/7/2013 in a worksheet cell , you can format it to show either only the date part 2/7/2013 or the combined part , which will then show 2/7/2013 12:00:00 AM , which is the start of 2/7/2013.


Thus , a date is actually the integer part of a date+time combination ; when you do INT(a date+time combination) you get only the date part , which can then be compared with the VBA system variable Date for equality. If you don't remove the time component , then the values in column F will never equal tempdate unless they happen to be 2/7/2013 12:00:00 AM.


Place your cursor anywhere in your VBA code , and press F8 to step through the code ; once the statement assigning the system variable Date to tempdate has executed , type in the following in the Immediate Window :


?tempdate="2/7/2013 12:00:00 AM"


and see what you get.


In addition to the above , I have cleaned up the code a bit , since there were two independent loops ; since the condition checks for the Project Status in the two loops were mutually exclusive , both the loops could be combined ; secondly , the calculation of the upper limit for the for loop ( ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row ) could be assigned to a variable called lastrow.


Narayan

 

[kaushik03]

@Hui:


Your below statement actually answers my question clearly.


"The date&time is less than today as that is true even for a Text Comparison".


Thank you for your continued help and support.


@Narayan:


Thank you for your detailed explanation.


Regards,

Kaushik