Find min 2 and max 2 cell values then highlight

[jpthelpguy]

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.

[DonkeyOte]

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.

[DonkeyOte]

Related x-post can be found here

[jpthelpguy]

Can't seem to get conditional formating running right with macros. Is there another way?

[mewingkitty]

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?

[jpthelpguy]

I'm trying to record a macros and do the conditional formating thru the macros. The conditions take but they either don't work right, meaning the highlight various cells, or I get an debug error.

[jpthelpguy]

I figured out what the issue is, the "Y" in the formula seems to make all the difference. When I run this on macros, the spread sheets are different each time. So that is why the conditional format goes off the charts, lol.

What is the Y related to in the formulas? When I change the Y to another column, it messes it all up.

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)

[jpthelpguy]

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?

[mewingkitty]

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.

[DonkeyOte]

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 ?

[jpthelpguy]

Yes, you are correct. I will give some of this a try. I think your suggestion to use VBA is best.

Thank you both for your time and help on this matter. I'm going to mark this as solved for now.

all the best...