Apply Formatting to Absolute Max and Min Values in Row

[PugnaxX]

I should give you some background on what I am doing, so that you may understand where I'm coming from...

I have a .csv file that test equipment dumps data into. When a new test is run, the data is pasted into the first empty row at the bottom of the body of data. .csv files are of a special type that is text only, it will not save formatting so, in order to make this data easy to read for operators, I have written a macro that upon opening the .csv file, the user can click a custom toolbar button tied to my macro.

My macro formats the .csv file, inserting a title, column labels, color, etc... and then automatically saves it as a regular old .xls file that will save the formatting. Each time a new test is run, then the .csd file gets a new row of data at the bottom of the body of data. Then the user clicks the toolbar button in the .csd file to generate the easier to read version of the data. This .xls file is continually written over (the updated .csv with new data is saved overtop of the original .xls file, to keep it up to date).

What I am trying to do is to format the cells that contain the absolute max and min values per row to have a thick, colored border. The problem is two-fold. The first of which is how to determine the absolute max and min. The second is how to get Excel to evaluate all rows that contain data. Any thoughts? Thanks for any input you can spare!!

 

[PugnaxX]

p.s. (this is all in Excel, of course)

 

[SkipVought]

Hi,

You don't need VBA Code to do this. It can be done via Conditional Formatting with formulas like this...
[tt]
=MIN(ABS($A2:$D2))=ABS(A2)
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PugnaxX]

Hi Skip,

Thanks for your quick response! What I'm concerned about is the fact that the file is dynamic. It's not always going to be a specific range to be evaluated. It's going to continually grow one row at a time. What I need is for each row to be evaluated that contains data. I need the absolute max and min for each row. Thanks for any ideas!

Pug

 

[SkipVought]

Several ways.

1. format the entire column range.

2. To acquire the data, use Data/Get External Data... and in Data/Data Range Parameters check Preserve cell formatting

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PugnaxX]

I like the format entire data column range idea and the conditional formatting with formulas, but I can't seem to get them to work to evaluate each row to format the abs max and min of each row. ?

 

[SkipVought]

If you're selecting, for instance columns A thru D...
[tt]
=MIN(ABS($A1:$D1))=ABS(A1)
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PugnaxX]

Switching gears a little...

Is there any way that I could evaluate only the last row of data appended to the file and grab the values of the absolute max and min and then display them in a pop-up message box?