pls help conditinal formating or vba

[Atomsmasher]

Hello i hope someone here can help me. I have large data file and i want to highlight results that are higher than the MRL column. I am only concerned about sample type = Effluent, analyte = cbod, ammonia, tss and ecoli and their results. If their results are higher than the MRL i want the result to be highlighted. I have tried conditional formatting and it did not work. Can someone help me please. Thank you. see attachment

[georgiboy]

Welcome to the forum

If you are looking for a code solution and you have alot of data then maybe an array loop will be in order:


Sub VarLoop()

Dim var As Variant
Dim rng As Range, x As Long

Set rng = Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row)
var = rng.Value

For x = LBound(var) To UBound(var)
If var(x, 2) = "Effluent" Or var(x, 2) = "Effluent Grab" Then
If InStr(var(x, 3), "Ammonia") Or InStr(var(x, 3), "CBOD") Or InStr(var(x, 3), "TSS") Or InStr(var(x, 3), "E coli") Then
If Replace(Replace(var(x, 4), "<", ""), ">", "") >= var(x, 6) Then
Range(Cells(x + 1, 1), Cells(x + 1, 6)).Interior.Color = vbYellow
End If
End If
End If
Next x
End Sub




Hope this helps

[p45cal]

What do you want to see in rows 23:25, especially row 25?

[Atomsmasher]

it did not work. maybe i am not doing it correctly.

[Atomsmasher]

that is TSS i forgot to put the mrl. so if it is Effluent or Effluent grab and it is TSS and the result is greater than MRL which is 1 then i want the result highlighted. Thank you.

[Atomsmasher]

It worked! thank you so much! everyone who looked at it and especially georgiboy thank you thank you! you are awesome!!!!!!

[Atomsmasher]

i added some more columns and rows. attached a larger file with the code in it. some how the code is highlighting results that are lower than the flag level.
is there a way to just highlight results that are higher than the flag level. if flag level is missing than ignore it. please help.
only concern with
type that is equal to Effluent or Effluent Grab
Analysis/analytes that are CBOD 5, TSS, Ammonia as N and E Coli Idexx
if their result is greater than flag level, highlight the row.

Please please help. Thank you thank you so much. This would really help i have three years of data.

[p45cal]

Some changes got introduced into the code since georgiboy supplied it:

"E coli IDEXX" has become " E coli IDEXX"
Remove the extra space.

Range(Cells(x + 1, 1), Cells(x + 1, 6)).Interior.Color = vbYellow
has become
Range(Cells(x + 1, 1), Cells(x + 2, 11)).Interior.Color = vbYellow
While the 11 is OK, that 2 should remain a 1

Separately, blank flag levels are not being ignored and values in column E with < and/or > in them are not properly being interpreted as numbers, so a few suggested amendments result in:
Sub VarLoop()

Dim var As Variant
Dim rng As Range, x As Long

Set rng = Range("A2:K" & Range("A" & Rows.Count).End(xlUp).Row)
var = rng.Value

For x = LBound(var) To UBound(var)
If Not IsEmpty(var(x, 11)) Then 'to ignore blank flag levels.
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Then
If InStr(var(x, 4), "Ammonia as N") Or InStr(var(x, 4), "CBOD 5") Or InStr(var(x, 4), "TSS") Or InStr(var(x, 4), "E coli IDEXX") Then
If CDbl(Replace(Replace(var(x, 5), "<", ""), ">", "")) > var(x, 11) Then 'CDbl to coerce strings to numbers
Range(Cells(x + 1, 1), Cells(x + 1, 11)).Interior.Color = vbYellow
End If
End If
End If
End If
Next x
End Sub

[Atomsmasher]

Thank you so much. I copied and pasted your code and it gave me a run time error 13. I don't know what that means. When i click debug it highlights the CDbl line.
Please help me i will be so grateful. Thank you so much again.

[p45cal]

Ahh, row 503, the only row with a blank result and a flag level. Try:
Sub VarLoop()

Dim var As Variant
Dim rng As Range, x As Long

'On Error Resume Next
Set rng = Range("A2:K" & Range("A" & Rows.Count).End(xlUp).Row)
var = rng.Value

For x = LBound(var) To UBound(var)
If Not IsEmpty(var(x, 11)) Then 'to ignore blank flag levels.
If Not IsEmpty(var(x, 5)) Then 'to ignore blank results
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Then
If InStr(var(x, 4), "Ammonia as N") Or InStr(var(x, 4), "CBOD 5") Or InStr(var(x, 4), "TSS") Or InStr(var(x, 4), "E coli IDEXX") Then
If CDbl(Replace(Replace(var(x, 5), "<", ""), ">", "")) > var(x, 11) Then 'CDbl to coerce strings to numbers
Range(Cells(x + 1, 1), Cells(x + 1, 11)).Interior.Color = vbYellow
End If
End If
End If
End If
End If
Next x
End Sub
I've added a commented-out line On Error Resume Next. If you get another error, try enabling this line and check the results.

[JKwan]

give this a go, row 503 - your result is a blank. I put in a condition to skip if blank - hope it is what you need


Sub VarLoop()
Dim var As Variant
Dim rng As Range, x As Long

Set rng = Range("A2:K" & Range("A" & Rows.Count).End(xlUp).Row)
rng.Interior.Color = xlNone
var = rng.Value

For x = LBound(var) To UBound(var)
If Not IsEmpty(var(x, 11)) Then 'to ignore blank flag levels.
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Then
If InStr(var(x, 4), "Ammonia as N") Or InStr(var(x, 4), "CBOD 5") Or InStr(var(x, 4), "TSS") Or InStr(var(x, 4), "E coli IDEXX") Then
If var(x, 5) <> "" Then
If CDbl(Replace(Replace(var(x, 5), "<", ""), ">", "")) > var(x, 11) Then 'CDbl to coerce strings to numbers
Range(Cells(x + 1, 1), Cells(x + 1, 11)).Interior.Color = vbYellow
End If
End If
End If
End If
End If
Next x
End Sub

[Atomsmasher]

thank you. it worked. Thank you so much JKwan, p45cal and Georgiboy.
All of you are awesome. Thank you.

[Atomsmasher]

Hello i am sorry to ask again. I fi were to add more sites in the var(x,3) = Aeration 1 and more analysis in the InStr(var (x, 4), MLSS can i just copy and edit stuff within the code?
Also if i have more than one Aeration like aeration 1, aeration 2 ...125, do i need to type that 125 times or can i just add %Aeration% and it will pick up all aeration sites?
I am sorry this is totally new. I am so grateful so many were able to help me. Thank you.

[p45cal]

Yes, you can do something along the lines of:
If InStr(var(x, 4), "Ammonia as N") Or InStr(var(x, 4), "CBOD 5") Or InStr(var(x, 4), "TSS") Or InStr(var(x, 4), "E coli IDEXX") Or InStr(var(x, 4), "MLSS") Then
and:
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Or var(x, 3) = "Aeration 1" Or var(x, 3) = "Aeration 2" Then

but…
the latter could get tedious.

There are 2 different techniques being used in georgiboy's code to determine a string match; in the case of var(x,3) he looks for a case sensitive exact match, and for var(x,4) he looks for a case sensitive match anywhere in the string being searched.
There are other techniques too: the operator Like (vba's built in basic flavour of RegEx), and full blown RegEx, and…
Which technique(s) you use depends on what your data are like (how variable they might be) and how choosy (specific) you want to be about what constitutes a 'hit'.

So to avoid getting into a long discussion about all the possibilities, can we narrow down the likely scenarios?:
When looking at the SampleName column:
How consistent are entries such as Aeration 1? could it be any of Aeration1, aeration1, aeration 1 (changes in spaces and case)? This could depend on how the data get's there; humans typing, or choosing from a dropdown, or purely machine-generated (with no human involvement at all), or some other way.
Maybe you don't care and any entry containing Aeration or aeration anywhere within it you'd want to be a hit. This would make the code really easy to write but be aware that the likes of: Bath U-bend (without aeration) would also be a hit.
Where you're looking for the range Aeration 1 to Aeration 125, would you want to exclude Aeration 126 and above? Would you ever want to exclude say Aeration 30 to Aeration 40 from that bigger range?
Would the word Aeration always be at the very start of the entry?

Looking at the Analyte column, much the same questions but I notice that you changed "E coli" to "E coli IDEXX". Why? It could be important for you.

As a taster, you might try changing:
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Then
to:
If var(x, 3) = "Effluent" Or var(x, 3) = "Effluent Grab" Or var(x,3) like "Aeration #*" Or var(x,3) like "Aeration ##*" Or var(x,3) like "Aeration ###*" Then
(This is meant to include as a hit any entry starting with Aeration, case sensitive, followed by a single space followed by 1 to 3 digits, followed by anything else (or nothing), so it would include Aeration 0 to Aeration 999.)
(untested).

[Atomsmasher]

First, thank you so much for taking the time to help me. I am so grateful. As you can already tell i am not good at Excel. I only know how to export data into excel from the testing equipment.
You are right about the Ecoli - sometimes the ecoli is tested in a different method. Sometimes the Idexx method. i should leave it as just "like" ecoli, right? just incase.
The effluent and effluent grab - Effluent : sometimes i place automatic composite samplers that collect water samples every two hours in a 24hr period from different sections of a lake or stream or river. The effluent grab is i go and grab a sample out of a stream.
Aeration - yes sometimes its without a space sometimes all caps. the data over the years was entered by alot of people and each of them either spelled it Aeration 1 or Aeration1 or aeration, there are so many variations. It is crazy. Thank you for mentioning it. Also even though right now its 1 -125 it could get bigger. Thank you again for mentioning it. You thought of everything :)

Another thing i was hoping after you mentioned human error and it got me thinking about mlss and mlvss. these numbers are manually entered. it has sample date and work order. Each mlss is then burned to find what the mlvss (dry weight). Sometimes the person entering the numbers enters them wrong like in reverse order. MLSS cannot be smaller than mlvss. mlvss sometimes is entered greater than mlss. is there a way to highlight if it is the same work order number and the mlss is smaller than mlvss to highlight it?

I will follow what you said about using "like" and will let you know.
All of you here have helped me tremendously. I cannot thank you enough. The fishes and aquatic life thank you :)
Thank you!

[p45cal]

Aeration - yes sometimes its without a space sometimes all caps. the data over the years was entered by alot of people and each of them either spelled it Aeration 1 or Aeration1 or aeration, there are so many variations. It is crazy.Case insensitive and with/without single space (untested):
If UCase(Var(x, 3)) = "EFFLUENT" Or UCase(Var(x, 3)) = "EFFLUENT GRAB" Or UCase(Var(x, 3)) Like "AERATION #*" Or UCase(Var(x, 3)) Like "AERATION ##*" Or UCase(Var(x, 3)) Like "AERATION ###*" Or UCase(Var(x, 3)) Like "AERATION#*" Or UCase(Var(x, 3)) Like "AERATION##*" Or UCase(Var(x, 3)) Like "AERATION###*" Then
I've not included this in the attached.


Another thing i was hoping after you mentioned human error and it got me thinking about mlss and mlvss. these numbers are manually entered. it has sample date and work order. Each mlss is then burned to find what the mlvss (dry weight). Sometimes the person entering the numbers enters them wrong like in reverse order. MLSS cannot be smaller than mlvss. mlvss sometimes is entered greater than mlss. is there a way to highlight if it is the same work order number and the mlss is smaller than mlvss to highlight it?In the attached is some conditional formatting in the Result column. There is a proviso for this to give you correct results:
It's not just the Wrk and Analyte that constitute MLSS/MLVSS pairs but also SampleName. There must be no more than 1 pair of rows where Wrk,Analyte,SampleName,[MLSS or MLVSS] match. Your data has this.

For you to see any highlighting, there has got to be this mistake; your data was good in this respect, so in the attached I've changed two cells: E47 and E59 to create the error and show the highlighting.
The attached file is your original file with macros removed, no macro having been run, and I've left columns M,N,O & P to show how I developed the conditional formatting formula (final version in column P, stages up to that in columns M,N & O). You can/should delete all those columns.

Turns out the file's now too big to attach here so a link: https://app.box.com/s/87ybrbz0hohnprxduectq7kx05wb1tfh
and the formula in conditional formatting:
=SUMPRODUCT(($G$2:$G$4155=G2)*($C$2:$C$4155=C2)*($D$2:$D$4155="MLVSS"),($E$2:$E$4155))>SUMPRODUCT(($G$2:$G$4155=G2)*($C$2:$C$4155=C2)*($D$2:$D$4155="MLSS"),($E$2:$E$4155))

[Atomsmasher]

Thank you so much it is beautiful. Amazing! I am so grateful to you. Thank you is not enough. I am so grateful. I just did it on 6 months worth of data in warp speed time. You are awesome. Thank you thank you. I have tears in my eyes :)