Conditional Formatting with condition formula

**coolzero** · 03-17-2010, 01:58 PM

Hello,

Just new here in the forum. Well, i would like to ask for assistance regarding the coditional formatting in Excel 2007.

I have a set of data in a certain column for example from D5:D370 with hours on it and a single cell with drop-down listed are the dates of march. i want to format the cells in column D with conditions that the values are higher than 30minutes and if the value in the drop-down March2 only.

I appreciate anyone's help. Thank you.

**NBVC** · 03-17-2010, 02:07 PM

How exactly are the times in D5:D370 entered? e.g. if you look in the formula bar after clicking a random cell within that range, what do you see in the formula bar?

**coolzero** · 03-17-2010, 02:41 PM

there is no definite times as the hours been entered. It is formatted as [h]:mm:ss. I just need those higher than 30minutes be formatted with red (for example) if the single cell for example in A2 is equal to March2.

**NBVC** · 03-17-2010, 02:51 PM

So select D5:D370 and go to Home tab, Select Conditional Formatting|New Rule

Select: Use a formula to determine which cells to format

Then enter formula: =AND($D5>TIME(0,30,0),$A$2="Mar2")

If that doesn't work because A2 is an actual 2010 date... then use formula:

=AND($D5>TIME(0,30,0),$A$2=DATE(2010,3,2))

click Format and choose from the Fill tab.