Why is my the CF and VBA in my spreadsheet not working properly???

Can someone help, i dont know why this wont do what its supposed to and im stuck....

I set it so if you double click on a box it turns green, or red depending on color already. when i input the dates and times they dont format correctly and i cant change them, ive been through the CF rules and they seem fine, the VBA seems fine, where am i going wrong????

Check Call problem.xlsm

Without an explanation of what behavior you are expecting it is very difficult to guess what you have in mind. I haven't the faintest idea what you are trying to show here, but I see a couple of problems in a conditional formatting rule. Your CF rule checking for LATE is clear enough. But you have a whole bunch of other rules that looks like copies and they have overlapping ranges. That's really going to cause you a problem. View the rules and select "Show formatting rules for: This worksheet" and you'll see what I mean. This may work but it's a lot cleaner to have one rule with the entire range. This usually happens when you start copying and pasting and inserting things.

This duplicated rule looks strange for two reasons. Let's look at the one that applies to D6.

Formula:

Please Login or Register  to view this content.

Here's where I think you have the real problem: The $ in D$6 says regardless of what row the cell is in, check what's in row 6. For example, when deciding whether to apply this formatting to the cell E10, which is A GLallombardo at 9:00, it's going to look at the cell for M Buxton for 9:00.

Also, the constant at the end is 12:10 AM. Calculating the current time minus the heading time and comparing it to 12:10 AM seems a little odd and I can't figure out what you are trying to do there.

It's an IF. That is not an error, but it is the equivalent of

Formula:

Please Login or Register  to view this content.

which would probably be clearer.

Also note the rules you have and the order in which they execute. If multiple rules are true then the last one will be applied last. I can't tell if you have the rules in the correct order for what you want to do.

Cell D11 is blank.

Row numbers in your formulas in the "time" range use $ for the row. That is not an error but it prevents you from being able to copy the formula to other cells. It is a best practice to avoid $ unless necessary. Also, the logic has a similar issue as the CF formula above. It is not an error but you are using IF to stack up two conditions and the FALSE alternatives are both blank. For the formula in D6, this is the logical equivalent of

Formula:

Please Login or Register  to view this content.

I rearranged the logical comparison there to make it clearer that you are testing to see if D$5 is between $B6 and $C6. This is a just a suggestion for readability, a matter of style.

I suggest you indent your code and add a little space to make it readable. Also, there is no need for variable t. You can just use Target, as you do in the rest of the code (see red text). However, none of this is causing a problem.

You use the variable a but it is undeclared and unset. This effectively causes you to blank out the cell that has been double clicked, regardless of what else you might do. See last line of code before End Sub.

Please Login or Register  to view this content.