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

 

[rassten]

Hi

Maybe someting like

Sub b_test()

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

For i = fr To 1 Step -1
    On Error Resume Next
    a = Application.WorksheetFunction.Search("r", Range("h" & i))
    On Error GoTo 0
    If a > 0 And Range("f" & i) > 1 Then
        Rows(i).Interior.Color = 255
        Range("J" & i) = "SDS sec 3"
    End If
    a = 0
Next i


For i = fr To 1 Step -1
    On Error Resume Next
    a = Application.WorksheetFunction.Search("43", Range("h" & i))
    On Error GoTo 0
    If a > 0 And Range("f" & i) > 0.1 Then
        Rows(i).Interior.Color = 255
        Range("J" & i) = "SDS sec 3"
    End If
    a = 0
Next i

End Sub

 

[Marcelo Branco]

An alternative with formulas

Try this in J5 (assuming headers in F4:I4)

=IF(OR(AND(F5>=1,ISNUMBER(SEARCH("R",H5))),AND(F5>=0.1,ISNUMBER(SEARCH(43,H5)))),"SDS sec 3","")

copy down

To highlight you can use:

Select F5:J21
Home > Conditional Format > New Rule > Use a formula to...

insert this formula
=$J5="SDS sec 3"

Format button
Fill --> pick a color
ok, ok

HTH

M.

 

[chadski778]

Sub b_test() works well but it has highlighted the "r" in EC Hazard (title of data column). Could you modify by making the condition "r" and "integer" in the same string?
Also would it be possible to format just the 5 columns and not the entire row?

Thanks

 

[rassten]

maybe

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

For i = fr To 5 Step -1
' 5 if headlines in row 4
    On Error Resume Next
    a = Application.WorksheetFunction.Search("r", Range("h" & i))
    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]

Is there a way you could use the 'find' function to make the macro only treat data below the headings "%Actual", "Generic Name", "EC Hazard" and "CAS Number"?

 

[Marcelo Branco]

Is there a way you could use the 'find' function to make the macro only treat data below the headings "%Actual", "Generic Name", "EC Hazard" and "CAS Number"?

Hi chadski,

If you prefer a macro ok, but let me ask: have you tried my formulas in #3?

M.

 

[chadski778]

It is still selecting my column headings even though "Actual%" is not a number!

 

[rassten]

try this

Sub b_2_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))
    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]

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