Conditional formatting can be vba

QuestionEdit

QUESTION: I have some basic knowledge of vba.

ok. heres what i have so far. 3 colors with conditional in each. yellow (5min), blue(15 min) and red(1hr). Here is example of script that ALMOST does it for the 15 min segments.

=OR(H5+H6>"0:15:00"+0,H6+H7>"0:15:00"+0)

The screenshot shows my wished for results, I have VBA in there now that adds the leading 0(Zero) to all of the times. My export is only as text. Each cell can only contain a max of 0:30:00. I need formatting for 3 data types, one over % min for rows I - P, one for 15 min in H, and one for 0:00:00 1hr in G. If any consecutive cells are => then highlight. I have received the above script that attempts to do it, yet it highlights additional zeros in some cases and not enough cells in others.

This is a messy one. I want this automated so that the individuals that use it do not have to highlight manually. A single individual must do this mainly for 20 different people daily.

Have not received workable answers from Mr Excel. the script used was from their assistance.

ANSWER: Hi Kelvin

Sorry I am not really following your question well.

Are you using conditional formatting with formula =OR(H5+H6>"0:15:00"+0,H6+H7>"0:15:00"+0)? Or are you using VBA?

I need your spreadsheet and example of where it is not working well. You may send it to my email.

---------- FOLLOW-UP ----------

QUESTION: Currently trying to use conditional formatting. but if there is a better or easier wass in VBA (im looking back thru my books) then that would be great. I am sending the actual sheet. thank you..

AnswerEdit

I looked at your spreadsheet and the conditional formatting. Instead of using a more complicated formula or VBA, it appears to me that the problem is due to poor design of the spreadsheet for the task.

In your spreadsheet, each row is in 30 mins time interval starting from 8am but the values in each column ("Time in Lunch", "Time in Break", ...) is formatted as time. It would be easier to work with values if you treat it as minutes (instead of time) and set it to a "Number" format. It is much easier to work with minutes for the calculation formula in conditional formatting.

You specified in your post that conditional formatting should format the cells in 3 different colors, yellow (5min), blue(15 min) and red(1hr).

However, looking at your formula in cell G5, we have =G5+G6+G7-G8>"1:00:00"+0. Why is the formula using values in 4 different cells instead of just the cell itself, G5? You can easily setup the formula to be =G5<=5 for yellow, =G5<=15 for blue, =G5<=60 for red if you use minutes in cell G5 (format it as number instead of time).

Hope this helps. Cheers.