Conditional Formatting Macro Issue

[PugnaxX]

I am trying to find the Max Absolute value of each row in a set of data and apply special formatting to that cell. The file constantly gets rows added to the end of the body of data and I need to integrate this operation into a macro. I have been trying to use Conditional Formatting while recording a macro from it. This is how I've been doing it:

I begin recording, select columns C through N and then apply my conditional formatting with this formula:

=MAX(ABS($C1:$N1))=ABS(C1)

This works to conditionally format all of the data, but when I run the macro I created from doing it, it conditionally formats the data incorrectly. I get a full column of specially formatted data in the C column only. So basically from the macro it is not going through each full row and picking the max value as it did when I conditionally formatted manually. Anyone know what may be happening here? Thanks for any help you can offer!!!

 

[PugnaxX]

p.s. This is in Excel of course

 

[urmein]

there is a simple solution to this problem (works at least on my MS Excel 2003 SP2 version).

Assuming your data are in column C, then click on that column header. This activates the FULL column C. Add then the following formula to the Conditional Formatting (keyboard: Alt-o, d).

=(MATCH(MAX(OFFSET(INDIRECT("$C$1"),0,0,COUNT($C:$C),1)),$C:$C,0)=ROW())

This formula turns TRUE for the particular cell in column C with the maximum absolute value.

Note: The INDIRECT function is required for the specific case of inserting a new row into column C above the current first row (obviosuly after having applied the Conditional Formatting formula). Otherwise, the INDIRECT function can be eliminated and simply substituted by $C$1.

 

[PugnaxX]

Thanks I'll check it out!!

 

[urmein]

There exists an even simpler solution, if you don't require a contiguous data array (i.e., data in consecutive rows).

My first formula assumes, and looks only at, a contiguous data array starting in cell C1. The OFFSET formula takes care of the dynamic change of the array length as you add new data.

Assuming multiple data arrays separated, for instance, by blank cells, the following formula returns the position of the overall maximum value of all data in column C:

=(MATCH(MAX($C:$C),$C:$C,0)=ROW())

So, depending on your specific needs you can choose different formulas. You can even envision the utilization of both formulas together to check for certain scenarios, such as the presence of empty cells (i.e. rows) between data. Both formulas return the same value only if there are no empty cells between data in column C.

 

[firefytr]

Why do you want to do this in a macro? The formula you had should work just fine. If you select all columns C:N (with C1 being the active cell) you could use ..

=(ROW()>1)*(MAX(ABS($C1:$N1))=ABS(C1))*(COUNTA($C1:$N1)<>0)

-----------
Regards,
Zack Barresse