Sorry if my questions will sound silly. I am not an excel expert and I really struggle to create a formula to highlight a cell. I am working with two different sheets on workbook: 1.Production & 2.Stock missing I am updating Production sheet with production order numbers (Column G). Production order is unique and can't repeat on this worksheet.

When someone have a problem with stock, they adding this number to second sheet (Stock missing) in column B. They are also clarifying Location of order in column F. Location of order could be "CW" or "TP"(nothing more). When problem is sorted for the particular production order, employees are choosing "YES" from drop down list in column K (the same "Stock missing" sheet).

What I want to do: I want to highlight production order number in RED (in "Production" Worksheet) if the production order number equals the same production order in "Stock missing" sheet and has location of order "CW", but when they choosing "Yes" in column K, color should disappear. But if location of order is "TP" i want to do the same as above, just highlight the cell in YELLOW. I was trying to find a formula that could help me, but with now luck....

Hope all of this make sense and there someone could help me. Please let me know is you need any more details. Probably it's an easy way of doing it, but I just lost the hope.. Thank you in advance

CodePudding user response：

Edited version:

Try creating these conditional formatting for entire column G:G in sheet Production

(Home->Conditional Formatting->New rule->Use formula to determine which cells to format):

Use this formula and set fill to "No fill":

=IF(Production!G1="",TRUE,INDEX('Stock missing'!K:K,MATCH(Production!G1,'Stock missing'!B:B,0),1)="Yes")

Use this formula and set fill to "Red":

=INDEX('Stock missing'!F:F,MATCH(G1,'Stock missing'!B:B,0),1)="CW"

Use this formula and set fill to "Yellow":

=INDEX('Stock missing'!F:F,MATCH(G1,'Stock missing'!B:B,0),1)="TP"

Now sort them as in picture and it should be working. Order of rules