How to check for Plus/Minus condition (Macro)

**Rick_Stanich** · 05-31-2007, 03:47 PM

With out using Conditional Formatting, how can I check a cells value if it can fall within a range of +\-.002?
I have this code but it seems to have limitations.

Please Login or Register  to view this content.

I am stumbling on how to check the cell to see if it complies to a +\-.002 scenario and if it does, clear the contents of another cell. The above code will clear the cell if the value is positive, but not negative.

All help is appreciated.

**rylo** · 05-31-2007, 07:20 PM

Hi

Bit confused from your code on what you are really trying to do, but from your words is this what you are chasing???

Please Login or Register  to view this content.

rylo

**Rick_Stanich** · 06-19-2007, 03:00 PM

Peering into my mind is dangerous!

Here is my scenario, I am attempting to loop thru many rows in two columns to check a condition.
The condition is a tolerance of +\-.002 of the value in cells from two columns, a range. I am taking the average value of the two cells and testing for conformance of +\-.002 from Zero. If the value is out of tolerance the code does something.

Conditional Formatting is limited on what I can do, or maybe its me that's limited?

The .ClearContents can be ignored, I resolved that one. (I feel like such a grown up).

**rylo** · 06-19-2007, 06:03 PM

Rick

Perhaps an example file showing what you want to happen for a given data set would help clarify.

rylo

**Rick_Stanich** · 06-21-2007, 09:51 AM

See attached Excel file, and please, criticism is accepted, however I have feelings

Also attached is the macro to run the data. I keep macros in a "personal.xls" file.

File "Template MPC 3061494-1 Profile .006-.004 JNxxxxx A.txt" needs to be renamed to "Template MPC 3061494-1 Profile .006-.004 JNxxxxx A.xls" and file "Module1_OOT_Profiles_A.txt" needs to be renamed to "Module1_OOT_Profiles_A.bas".

**Rick_Stanich** · 06-21-2007, 10:04 AM

I should have zipped both files :shrug:
Both files in a ZIP format.

**rylo** · 06-21-2007, 07:00 PM

Rick

Can you zip the spreadsheet(s).

rylo

**Rick_Stanich** · 06-22-2007, 01:34 PM

Here attached, is the excel file with the macro imported and tied to a Button on the spread sheet.
The macro only clears cell contents with no data is available. I have populated the cells with data, not real data.
My goal is to ensure validation of a condition, example, +\-.0020.

**mikerickson** · 06-22-2007, 03:08 PM

Have you considered a custom boolean function.

Please Login or Register  to view this content.

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

**Rick_Stanich** · 06-22-2007, 04:18 PM

This is the direction I need!

I can do quite a bit in VB/VBA, but I do not know everything!

**Rick_Stanich** · 06-25-2007, 02:25 PM

Originally Posted by mikerickson

Have you considered a custom boolean function.

Please Login or Register  to view this content.

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Add one more item to the "Do not know list".
How is this function used?

**mikerickson** · 06-26-2007, 01:08 AM

How to use it? Replace Excel's boolean function "=" with the boolean function almostEqual.

I haven't looked at your sheet yet (busy week), but based on first post:
This routine will color those cells in column D that are within .002 of the value in column F.

Please Login or Register  to view this content.

The same could be gotten with Conditional Formatting set to custom formula =almostEqual($D1,$F1) in D1 and copied to the rest of the column

The custom forumula =(ABS($D1-$F1)<.002) would do the same, but without the UDF.

I hope this helps.