Conditional formatting issue.

[strongman86]

hi guys,

I can't seem to get conditional formatting working please help. Cells G/H/I/J contain formulas referrencing other cells in sheet.

	A	B	C	D	E	F	G	H	I	J
1								=IFS(I1="Yes",G1,I1="No","No")	=IF(OR(ISNUMBER(SEARCH("Apple",E1)),ISNUMBER(SEARCH("Apl",E1)),ISNUMBER(SEARCH("Banana",E1))),"No","Yes")	=TEXT(VLOOKUP(C1,XXX!$A$2:$U$10007,21,0),"0000000")
2								=IFS(I2="Yes",G2,I2="No","No")	=IF(OR(ISNUMBER(SEARCH("Apple",E2)),ISNUMBER(SEARCH("Apl",E2)),ISNUMBER(SEARCH("Banana",E2))),"No","Yes")	=TEXT(VLOOKUP(C2,XXX!$A$2:$U$10007,21,0),"0000000")

I am applying conditional formatting to column J which would compare values: if I="Yes" & J=H then highlight J also it needs to highlight J if other 3 criterias are met: H = "No" & I = "No" & J is blank ( appear blank when formula =TEXT(VLOOKUP(C1,XXX!$A$2:$U$10007,21,0),"0000000") doen't return any value. But when H = "No" & I = "No" & J has come value returned don't highlight.
In short as per below:
J1=Highlight
J2=Don't highlight
J3=Don't highlight

	A	B	C	D	E	F	G	H	I	J
1								1966394	Yes	1966394
2								No	No
3								No	No	7654321

 

[strongman86]

Sorry,
I meant:
J1=Highlight
J2=Highlight
J3=Don't highlight