draw red oval around cell based on multiple values

[abdelfattah]

hello
I need macro to draw red oval based on multiple condition the range in columns a,b,c,d,e should draw for words and values (abc,fired, <50) and if is more than column contain oval then write in column f closed (bold, red font )
this is what I expected

SHAPE.xlsm
	A	B	C	D	E	F
1	name	case1	case2	case3	case4	FINAL
2	Ali	20	54	abs	fired	CLOSED
3	Omar	40	73	pre	existed
4	Ahmed	50	34	abs	existed	CLOSED
5	Mahmoud	60	22	abs	fired	CLOSED
SS

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A7,A10:A1048576	Cell Value	duplicates	text	NO

I insert oval around cell but when I insert image not show you can depend on my explanation above
thanks in advance

 

[sijpie]

Hi Abdel,

why are you trying to do this with a shape? Why not use conditional formatting?

The formula in column G is:
G2: =IF(SUM(IF(C2<50,1,0),IF(D2<50,1,0),IF(E2="abs",1,0),IF(F2="fired",1,0))>1,"CLOSED","")

The conditional formatting for columns C2:Dxx: =C2<50
The conditional formatting for columns E2:Fxx: =OR(E2="abs",F2="fired")
and then set the format with red border and light red fill

 

[abdelfattah]

hi, sijpie I find difficulty with condition formatting and is is problem with oval shape ? moreover i have a problem with the first condition and the last condition
this is what i got after use all of formulas

SHAPE.xlsm
	A	B	C	D	E	F
1	name	case1	case2	case3	case4	FINAL
2	Ali	20	54	abs	fired
3	Omar	40	73	pre	existed
4	Ahmed	50	34	abs	existed
5	Mahmoud	60	22	abs	fired
SS

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E5	Expression	=OR(D2="abs";E2="fired")	text	NO
B2:C5	Expression	=B2<50	text	NO
F:F	Expression	=IF(SUM(IF(C2<50;1;0);IF(D2<50;1;0);IF(E2="abs";1;0);IF(F2="fired";1;0))>1;"CLOSED";"")	text	NO
A1:A7,A10:A1048576	Cell Value	duplicates	text	NO

 

[sijpie]

OK, I made an error copying your data. I added a column.
But you also made an error.

First: The Formula (NOT conditional formatting ) for F2:
=IF(SUM(IF(B2<50;1;0);IF(C2<50;1;0);IF(D2="abs";1;0);IF(E2="fired";1;0))>1;"CLOSED";"")

copy this down column F

Then for the conditional formatting:
Modify the one for column F to:
=F2="CLOSED"
Modify the one for D2:Ex to:
=OR(D2="abs";D2="fired")