Returning a value in a cell under specific conditions

[chadski778]

I have a selection of data in four columns (F-I). I would like to return a value in column J (text - "SDS sec 3") if the value in column H contains an "R" AND the value in column F is >=1 or if the value in column H contains "43" AND the value in column F >=0.1. I would also like to format the entire row (4 columns) if the conditions are met. See example below.

Excel Workbook
	A	B	C	D
2		BEFORE
3
4
5	Actual%	Generic Name	EC Hazard	CAS Number
6	74.92	Highly refined mineral oil (IP346	N/C	Various
7	74.92	Highly refined mineral oil (IP346	N/C	64742-62-7
8	74.92	Highly refined mineral oil (IP346	N/C	64742-65-0
9	10	Highly refined mineral oil (IP346	N/C	64742-54-7 or 92062-09-4
10	5.852	Highly refined mineral oil (IP346	N/C	64742-65-0
11	3.99	Polyolefin polyamine succinimide polyol	R53	Polymer
12	1.064	Calcium alkaryl sulphonate	R53	Not available
13	1.064	Zinc dialkyldithiophosphate	R41-51/53	68649-42-3
14	1.06	Olefin co-polymer	N/C	Confidential
15	0.99	Highly refined mineral oil (IP346	N/C	Not available
16	0.99	Polyalkylmethacrylate	N/C	Not available
17	0.931	Calcium alkaryl sulphonate	R43-53	Not available
18	0.399	Borated polyolefin polyamine succinimide	R53	Not available
19	0.133	Polyolefin polyamine succinimide molybdenum cmplx	N/C	Polymer
20	0.0532	Polyalkarylamine	R51/53	68411-46-1
21	0.0266	Branched alkanes	R38-65-51/53	Polymer
22	0.01	Alkyl methacrylate (as impurity)	R36/37/38-50/53	142-90-5
Declarations

Excel Workbook
	A	B	C	D	E
25		AFTER
26
27	Actual%	Generic Name	EC Hazard	CAS Number
28	74.92	Highly refined mineral oil (IP346	N/C	Various
29	74.92	Highly refined mineral oil (IP346	N/C	64742-62-7
30	74.92	Highly refined mineral oil (IP346	N/C	64742-65-0
31	10	Highly refined mineral oil (IP346	N/C	64742-54-7 or 92062-09-4
32	5.852	Highly refined mineral oil (IP346	N/C	64742-65-0
33	3.99	Polyolefin polyamine succinimide polyol	R53	Polymer	SDS sec 3
34	1.064	Calcium alkaryl sulphonate	R53	Not available	SDS sec 3
35	1.064	Zinc dialkyldithiophosphate	R41-51/53	68649-42-3	SDS sec 3
36	1.06	Olefin co-polymer	N/C	Confidential
37	0.99	Highly refined mineral oil (IP346	N/C	Not available
38	0.99	Polyalkylmethacrylate	N/C	Not available
39	0.931	Calcium alkaryl sulphonate	R43-53	Not available	SDS sec 3
40	0.399	Borated polyolefin polyamine succinimide	R53	Not available
41	0.133	Polyolefin polyamine succinimide molybdenum cmplx	N/C	Polymer
42	0.0532	Polyalkarylamine	R51/53	68411-46-1
43	0.0266	Branched alkanes	R38-65-51/53	Polymer
44	0.01	Alkyl methacrylate (as impurity)	R36/37/38-50/53	142-90-5
Declarations

Thanks

Chad

 

[chadski778]

Thank you so much. That is brilliant!

 

[Marcelo Branco]

Marcelo, hi i need a macro because i want to use it in another bigger macro. Thanks for your help. It is almost there!

Ok, good luck!

m.

 

[chadski778]

Rassten, when I have "Various" in the EC Hazard column it is being selected by the macro, probably because of the "r" in the word. Any chance of making the word "various" not be selected?

Thanks

 

[rassten]

Try this

Sub b_3_test()
fr = Cells(Rows.Count, 6).End(xlUp).Row

For x = fr To 1 Step -1
    If Range("f" & x) = "Actual%" Then y = x + 1
Next x
    

For i = fr To y Step -1

    On Error Resume Next
    a = Application.WorksheetFunction.Search("r", Range("h" & i))
    If Range("h" & i) = "Various" Then a = 0
    b = Application.WorksheetFunction.Search("43", Range("h" & i))
    On Error GoTo 0
    If (a > 0 And Range("f" & i) > 1) Or (b > 0 And Range("f" & i) > 0.1) Then
        Range("f" & i).Resize(1, 5).Interior.ColorIndex = 36
        Range("J" & i) = "SDS sec 3"
    End If
    a = 0
    b = 0
Next i

End Sub

 

[chadski778]

Sten, thank you that works fine

 

[chadski778]

Sten, could you add to this macro so that it highlights rows for the following extra conditions
If any cells in range B1:B60 has an interior colour of 255 AND the value in column F is >0.1 and there is a "26" or "27" or "28" or "29" or "50/53" or "51/53" in column H in the same row

 

[rassten]

Maybe something like:

Sub a_test()
For i = 1 To 60 Step 1
    If Range("b" & i).Interior.Color = 255 Then
        If Range("f" & i) > 0.1 Then
            If Range("h" & i) = 26 Or Range("h" & i) = 27 _
            Or Range("h" & i) = 28 Or Range("h" & i) = 29 _
            Or Range("h" & i) = "50/53" Or Range("h" & i) = "51/53" Then
                Range(i & ":" & i).EntireRow.Interior.Color = RGB(255, 255, 0)
            End If
        End If
    End If
Next i
End Sub

 

[chadski778]

How would i combine the two pieces of code you provided me to include all IF statements?

CODE 1

fr = Cells(Rows.Count, 6).End(xlUp).Row
For x = fr To 1 Step -1
If Range("f" & x) = "Actual%" Then y = x + 1
Next x

For i = fr To y Step -1
On Error Resume Next
a = Application.WorksheetFunction.Search("r", Range("h" & i))
If Range("h" & i) = "Various" Then a = 0
b = Application.WorksheetFunction.Search("43", Range("h" & i))
On Error GoTo 0
If (a > 0 And Range("f" & i) > 1) Or (b > 0 And Range("f" & i) > 0.1) Then
Range("f" & i).Resize(1, 5).Interior.ColorIndex = 36
Range("J" & i) = "SDS sec 3"
End If
a = 0
b = 0
Next i

AND

CODE 2

For i = 1 To 60 Step 1
If Range("b" & i).Interior.Color = 255 Then
If Range("f" & i) > 0.1 Then
If Range("h" & i) = 26 Or Range("h" & i) = 27 _
Or Range("h" & i) = 28 Or Range("h" & i) = 29 _
Or Range("h" & i) = "50/53" Or Range("h" & i) = "51/53" Then
Range(i & ":" & i).EntireRow.Interior.Color = RGB(255, 255, 0)
End If
End If
End If
Next i