Solved: correction

[oleg_v]

hi
i have a problem with a macro in the file that i attached i have a macro that
color the numbers that are out of tolerance
i need some help with a macro that after the coloring of the numbers the macro will replace the colored number that out of tolerance with the correct one

thanks

[Bob Phillips]

What are the rules for calculating the correct tolerance?

[oleg_v]

hi
forth row + fifth row = upper limit
forth row + sixth row = lower limit

all data between those limits is the correct data

[p45cal]

hi
forth row + fifth row = upper limit
forth row + sixth row = lower limit

all data between those limits is the correct data

So the 'correct' data can be anything in that range; so would you like that to be simply the value in the fourth row? Or at the limit of tolerance in the direction is was initially out in? Or somewhere inbetween?

Sounds like the inspection results are being fiddled!

By the way, your macro can be simplified:[VBA]Sub colorRow()
Dim i As Long, t As Long
Dim FinalRow As Long
With Sheets("oleg3")
For t = 2 To 37 'column numbers
myVar = .Cells(4, t).Value
myVar1 = .Cells(6, t).Value
myVar2 = .Cells(5, t).Value
FinalRow = .Cells(.Rows.Count, t).End(xlUp).Row
For i = FinalRow To 1 Step -1 'why step backwards?
If .Cells(i, t) < (myVar + myVar1) Or .Cells(i, t) > (myVar + myVar2) Then .Cells(i, t).Interior.ColorIndex = t + 3
Next i
Next t
End With
End Sub[/VBA]

[oleg_v]

Hi
thanks for the advise.
the results need to be somewhere between the tolerance.
the results are not beeing fiddled.
i need make statistical data for the data with rejected parts and without
after that i need to explain the subcontractor to repair the process and give him an example of his process without rejected parts and how it should be
that is the reason that i need to make the correction.

thanks.

[p45cal]

This will put in a value somewhere at random between the tolerance limits (there'll be more decimal places but you can format the cells differently):[vba]Sub colorRow()
Dim i As Long, t As Long
Dim FinalRow As Long
With Sheets("oleg3")
For t = 2 To 37 'column numbers
myVar = .Cells(4, t).Value
myVar1 = .Cells(6, t).Value
myvar2 = .Cells(5, t).Value
FinalRow = .Cells(.Rows.Count, t).End(xlUp).Row
For i = FinalRow To 7 Step -1 'why step backwards?
If .Cells(i, t) < (myVar + myVar1) Or .Cells(i, t) > (myVar + myvar2) Then
.Cells(i, t).Interior.ColorIndex = t + 3 'remove this line?
.Cells(i, t).Value = myVar + myVar1 + (myvar2 - myVar1) * Rnd
End If
Next i
Next t
End With
End Sub
[/vba]

[oleg_v]

hi
in tried to format cells to decrease the decimal places but without successes
in thew formula bar i am still seeing the hole number with a large amount of decimal places.

[p45cal]

In the formula bar you will, but not on the sheet, does that matter?

[oleg_v]

no it does not
but is there a way to change it?

[Aussiebear]

no it does not
but is there a way to change it?

And that about sums up this topic.