Good day gentlemen,
I am a novice VBA user and I'm finding that the simple things are often the most difficult. That or I'm having a brain fart...
I am creating a template for my work. As the user fills in data, the template responds with worksheet_change events giving the user feedback.
In this specific case, a data set of 5 points are entered. The conditional formatting changes the color of the cells, green if good, red if bad. This all works. In addition to this, the template gives an average of the 5 points and this value is compared to a nominal value.
If the average value is above or below a predefined range of values, then a 3rd cell displays the average with **#** format.
The issue I have is that if ANY of the 5 data point AND/OR the average value is out or range, then the 3rd cell must append the ** to the average value in the 3rd cell....and it's not working. The code I have will append the ** if the actual average is out of range, but Not if the data points are out.
This code is what I have now. It half works, but I need to figure out how to say IF ANY of the values in the target range is true, then append **. But it's only working if the final cell in the range "B27" matches the criteria.
Thank you for any assistance you can provide
I am a novice VBA user and I'm finding that the simple things are often the most difficult. That or I'm having a brain fart...
I am creating a template for my work. As the user fills in data, the template responds with worksheet_change events giving the user feedback.
In this specific case, a data set of 5 points are entered. The conditional formatting changes the color of the cells, green if good, red if bad. This all works. In addition to this, the template gives an average of the 5 points and this value is compared to a nominal value.
If the average value is above or below a predefined range of values, then a 3rd cell displays the average with **#** format.
The issue I have is that if ANY of the 5 data point AND/OR the average value is out or range, then the 3rd cell must append the ** to the average value in the 3rd cell....and it's not working. The code I have will append the ** if the actual average is out of range, but Not if the data points are out.
Code:
Dim WSTol As Worksheet
Set WSTol = Worksheets("5 Readings")
Dim B27 As String
B27 = Format(WSTol.Cells(27, "B").Value, "Standard") 'keep the 00.00 format
For Each C In WSTol.Range("B21:B25", "B27").Cells
If C.Value < WSTol.Cells(4, "L") Or C.Value > WSTol.Cells(4, "M") And C.Value <> 0 Then
WSTol.Cells(29, "B") = "**" & B27 & "**"
Else: WSTol.Cells(29, "B") = B27 'all data points & the average are within range
End If
Next
This code is what I have now. It half works, but I need to figure out how to say IF ANY of the values in the target range is true, then append **. But it's only working if the final cell in the range "B27" matches the criteria.
Thank you for any assistance you can provide