Find min 2 and max 2 cell values then highlight

I have searched, googled, and just can 't find the right code for this question, I hope someone can help me.

I want to identify the 2 minimum valued greater than 0.00% in rows with percentages only. Then identify the 2 maximum values greater than 0.00% in rows with percentages only. The sheet changes in size (rows and columns) each day. C4 Row 4 is a constant starting place if that is necessary. I have been tweaking this code all night, but no luck. I keep getting a highlighted yellow error in editor @ oRg.Find(What:=iMin, _
I also don't know what an oRg is, I am using 2001, that may be the problem.
Please help...

Please Login or Register  to view this content.

I'm not actually sure your sample is correct, eg: Q10 should be pink as opposed to N10 as it's the 2nd smallest value

Highlight C3:Y50 and enter the following 2 conditions:

Top 2

=AND(MOD(ROWS(C$3:C3),2)=0,N(C3),SUM(IF(($C3:$Y3<>0)*($C3:$Y3<C3),1))<2)

Bottom 2

=AND(MOD(ROWS(C$3:C3),2)=0,N(C3),SUM(IF(($C3:$Y3<>0)*($C3:$Y3>C3),1))<2)

I'm not entirely convinced VBA is required here (given we've shown you can build excess capacity into the CF range), however, if you do need VBA post back and we can show how you can apply the above.

Related x-post can be found here

Should be able to record a macro and use relative cell references when recording it, just make sure you move down to the start of the next row/ rows, etc.

however...

Are you trying to use code to act as a custom conditional formatting alternative, or using a macro to set up conditional formatting across the sheet for you?

A better way of asking this question would be:

If I change the "Y" what esle needs to change with the formula?


However, with that said, it still is not running well with a recorded macros. I am only able to do it manually, but only if I paste to Y50. Is there a better way or a way to use a macros to get this done?

The only way I can come up with is (as always for me) with functions only applicable in 2007. I wrote some code that'll select any cell with % formatting (minus the one at the far right) and ignore 0's then conditionally format the remaining cells, based on how many there are, to either have the top and bottom 2 hightlight, or the top and bottom 1 highlight (if there are less than 4 cells which are not 0). This involves non-contiguous conditional formatting, which is not functional in 2003. So basically I'm out of ideas at the moment.

jpthelpguy,

if you want to do this with VBA you will need to use either a Worksheet_Change event or a Worksheet_Calculate event - which would be determined by how the values of interest are altered... if manually done then you can use the _Change event, if the values are the results of formulae updating then you must use a Calculate event.

I have shown you can do what you want using Conditional Formatting formulae (compatible across all versions) which can be applied across you entire range simultaneously from C3 to Yx where x can be row 100, 1000 whatever - ie build in some excess capacity... that said... those formulae are obviously set to Y... which as you say will not be constant... so to go back to your other post(s) - C3 is constant ... bottom right is not, correct ?