# Formula not working



## Geek Girl 007 (Jan 7, 2023)

Hi all, 
I want the line to go another colour if column D has any one of these in it:

UA
Sick
Covid
Other
I wrote this formula but it's not working  
*=IF(OR(K14={"UA","Sick","Covid","Other"})
DETAIL-TEST.xlsxABCD1AM2ICNAME31NAME42NAME53NAME64NAME75NAMESheet2Cells with Conditional FormattingCellConditionCell FormatStop If TrueC2:D7Expression=$D2="Sick"textNOCells with Data ValidationCellAllowCriteriaD2:D7List=TEXT!$D$2:$D$5*


----------



## jasonb75 (Jan 7, 2023)

Formula looks fine apart from referencing K14 where it should be $D2


----------



## Peter_SSs (Jan 7, 2023)

A couple of things.


Your IF function has no results for the True or False condition & does not have matching opening/closing parentheses.
If you are trying to use that in your Conditional Formatting you will run into problems anyway as you cannot use array constants in CF
Since you have Data Validation in column D, aren't you just trying to colour cells if column D is anything but blank? If so ..

Geek Girl 007.xlsmCD12NAME3NAME4NAMECovid5NAME6NAMEOther7NAMESheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueC2:D7Expression=$D2<>""textNOCells with Data ValidationCellAllowCriteriaD2:D7List=TEXT!$D$2:$D$5

If you are specifically looking for one of those values in D2:D5 of TEXT then

Geek Girl 007.xlsmCD12NAME3NAME4NAMECovid5NAME6NAMEOther7NAMESheet2Cells with Conditional FormattingCellConditionCell FormatStop If TrueC2:D7Expression=MATCH($D2,TEXT!$D$2:$D$5,0)textNOCells with Data ValidationCellAllowCriteriaD2:D7List=TEXT!$D$2:$D$5


----------



## kvsrinivasamurthy (Jan 7, 2023)

Try for CF
*=OR(K14="UA",K14="Sick",K14="Covid",K14="Other"})*


----------



## jasonb75 (Jan 7, 2023)

jasonb75 said:


> Formula looks fine apart from referencing K14 where it should be $D2


I must have had my eyes closed when I looked at it first time, I didn't see IF at the start of the formula, just remove that and use OR with the array on the correct cell reference and all will be good.
	
	
	
	
	
	



```
=OR($D2={"UA","Sick","Covid","Other"})
```


----------



## Geek Girl 007 (Jan 7, 2023)

Peter_SSs said:


> A couple of things.
> 
> 
> Your IF function has no results for the True or False condition & does not have matching opening/closing parentheses.
> ...


This makes more sense, as yes you are correct, I only want colour if it's not blank. Thank you


----------



## Geek Girl 007 (Jan 7, 2023)

jasonb75 said:


> =OR($D2={"UA","Sick","Covid","Other"})


This is what happened when I put this in.


----------



## Peter_SSs (Jan 7, 2023)

Geek Girl 007 said:


> This makes more sense, as yes you are correct, I only want colour if it's not blank. Thank you


You're welcome. 



Geek Girl 007 said:


> This is what happened when I put this in.


Yes, I mentioned in post 3 that you cannot use array constants (ie {.. , .. , ..}) in conditional formatting.


----------

