Hightlight Lowest Cell in Column, not Value

[mdillona]

Hey,

Overview and goal: I'm looking for something that will help with commission approvals. Rather than going through and highlighting the cell that is being paid, I'd like it to automatically do that depending on the values in 2 cells.

I'd like to highlight the lowest cell in a column (only 2 cells). Both cells are formulated that is looking at two rows and pulling the last # that appears in that range{=LOOKUP(9.99999999999999E+307,$D$41:$O$41) & =LOOKUP(99^99,$D$41:$O$42)}.

Here's an example (changed all data) of the spreadsheet. So, this (cells with $494.54 and $384.64) selects the most current month and the cell with a value. What I'm now trying to do is highlight the lowest cell (not value) between those two cells. I tried the conditional formating to get the cell for PP2, but since there is a formula, it highlights at all times when set to greater than 0. I have yet to find anything for PP1 as PP1 looks for PP2 to determine if it should be highlighted or not.
[TABLE="width: 139"]
<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY>[TR]
[TD="class: xl67, width: 78, bgcolor: transparent"]PP1[/TD]
[TD="class: xl68, width: 106, bgcolor: transparent"] $ 494.54 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PP2[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 384.64 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Total[/TD]
[TD="class: xl65, bgcolor: transparent"] $ 879.18 [/TD]
[/TR]
</TBODY>[/TABLE]



[TABLE="width: 875"]
<COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=2 width=81><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=3 width=81><TBODY>[TR]
[TD="class: xl70, width: 181, bgcolor: transparent"]Override Bonus[/TD]
[TD="class: xl68, width: 82, bgcolor: transparent"]Jan[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Feb[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]Mar[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]Apr[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]May[/TD]
[TD="class: xl68, width: 86, bgcolor: transparent"]Jun[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Jul[/TD]
[TD="class: xl68, width: 78, bgcolor: transparent"]Aug[/TD]
[TD="class: xl68, width: 106, bgcolor: transparent"]Sep[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]Oct[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]Nov[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]Dec[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PP1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 543.99 [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 486.30 [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 337.94 [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 703.35 [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 1,087.99 [/TD]
[TD="class: xl65, bgcolor: transparent"] $ 494.54 [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PP2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 428.60 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 420.36 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 445.09 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 703.35 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 461.57 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 384.64 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Monthly Total[/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 972.60 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 906.66 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 783.02 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 1,406.69 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 1,549.56 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ 879.18 [/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[TD="class: xl67, bgcolor: transparent"] $ - [/TD]
[/TR]
</TBODY>[/TABLE]



Any help you can provide would be great!

Thanks,
Dillon

 

[Momentman]

Just to clarify, Highlight the lowest cell between the 2 cells? Which cells would that be based on your example above?

 

[mdillona]

Yes, the two cells are referencing the section. I put that in there to show that it was a formula (poor decision on my part). So, there are two different sections. The first part of the example references the rows below. So, lets assume the first section is cells L10 and L11 and the second section covers area D41:O42 (only $ between Jan-Dec and PP1&PP2). Hope that makes sense.

Thanks for the help!!