Conditional Formula to Highlight In Red a Change in Text

[mlnet]

Hello,

I am trying to find a way to identify updates in a column using a Conditional Formula.

I have 3 columns, WO ID, WO Flag and Package Counter. The counter is counting the number of common WO IDs. Next, I would like to create a conditional formula to highlight (in red) when text in the WO Flag column changes.

For example, I have 4 WO IDs for WO 54833. I have 3 stages that a WO ID can change to, First, Second and Third. If one of the WO Flag items change to “Third”, I need to highlight the text to Red.

Sample Data:
[TABLE="width: 266"]
<tbody>[TR]
[TD]WO ID
[/TD]
[TD]WO Flag
[/TD]
[TD]Package Counter
[/TD]
[/TR]
[TR]
[TD]WO 54833
[/TD]
[TD]First
[/TD]
[TD] 4
[/TD]
[/TR]
[TR]
[TD]WO 54833
[/TD]
[TD]Second
[/TD]
[TD] 4
[/TD]
[/TR]
[TR]
[TD]WO 54833
[/TD]
[TD]Second
[/TD]
[TD] 4
[/TD]
[/TR]
[TR]
[TD]WO 54833
[/TD]
[TD]Second
[/TD]
[TD] 4
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mlnet]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

What do you mean by "if I filtered on Column C"? As your table above now shows the formula "=AND(C3="Third",C3<>B3)" should now be residing in E3, and so the formula in the Conditional Formatted cells should be reading from =AND(E3="Third",E3<>E3).

Anyway let's move on. In G3 apply this formula: =IF(D3=E3,"Packaged","Unpackaged") and then drag it down the column.
THEN go to Conditional Formatting as before and enter the formula: "=D3<>E3".
I think that will show what you seek.

Hi Brian,

Thank you so much for the =IF(D3=E3,"Packaged","Unpackaged") formula and the conditional formatting formula "=D3<>E3". This works great. Only one tiny issue.
This formula is adding "Packaged" to all cells even the blank ones. How can I have it ad UnPackaged to blank cells in Column D (WO Code) and Column E (Refreshed WO Code)?

[TABLE="width: 478"]
<tbody>[TR]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[/TR]
[TR]
[TD]WO IDs
[/TD]
[TD]WO Code
[/TD]
[TD]Refreshed WO Code
[/TD]
[TD]Counter
[/TD]
[TD]Counter Status
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]First
[/TD]
[TD]First
[/TD]
[TD]6
[/TD]
[TD]Packaged
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]Packaged
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]Packaged
[/TD]
[/TR]
[TR]
[TD]WO1386867
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]Packaged
[/TD]
[/TR]
[TR]
[TD]WO1386868
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]Packaged
[/TD]
[/TR]
[TR]
[TD]WO1386869
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Packaged
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hazel22]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

You can set a conditional formula in WO flag. Highlight cell rules if equal to "third". Is that what you need?

 

[NARAYANK991]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

Hi ,

I am not sure I have understood you , but wouldn't this Conditional Formatting formula work ?

=AND($B2 = "THIRD", SUMPRODUCT(($A$2:$A$5 = $A2) * ($B$2:$B$5 = "SECOND")) > 0)

Select your range of data , say A2:C20 , and then apply this rule.

This should colour the entire row of data. If you do not want this to happen , then select only the data range in column B , say B2:B20 , before applying the same formula.

 

[BrianJN1]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

Hm. I didn't save my test sheet so I'm going to have to rebuild and rethink.

Let me understand our criteria:
If First = First then Packaged,
If Second= Second then Packaged, but
If Second=! Third then Unpackaged and then
If a WO appears as blank then Unpackaged.

Therefore what we need is, if we have blank entries or "Third", "Unpackaged" is to be reported in RED?

 

[mlnet]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

Hm. I didn't save my test sheet so I'm going to have to rebuild and rethink.

Let me understand our criteria:
If First = First then Packaged,
If Second= Second then Packaged, but
If Second=! Third then Unpackaged and then
If a WO appears as blank then Unpackaged.

Therefore what we need is, if we have blank entries or "Third", "Unpackaged" is to be reported in RED?

Hi Brian,

Yes to all but I don't need the "Unpackaged" for blank WO Codes to turn red. For all blank WO Codes, the default would be "Unpackaged".

[TABLE="width: 433"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G [/TD]
[/TR]
[TR]
[TD]WO IDs[/TD]
[TD]WO Code[/TD]
[TD]Refreshed WO Code[/TD]
[TD]Counter[/TD]
[TD]Counter Status[/TD]
[/TR]
[TR]
[TD]WO1386866[/TD]
[TD]First[/TD]
[TD]First[/TD]
[TD]6[/TD]
[TD]Packaged[/TD]
[/TR]
[TR]
[TD]WO1386866[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]Packaged[/TD]
[/TR]
[TR]
[TD]WO1386866[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]Packaged[/TD]
[/TR]
[TR]
[TD]WO1386867[/TD]
[TD]Second[/TD]
[TD]Third[/TD]
[TD]6[/TD]
[TD]Unpackaged[/TD]
[/TR]
[TR]
[TD]WO1386868[/TD]
[TD]Second[/TD]
[TD]Third[/TD]
[TD]6[/TD]
[TD]Unpackaged[/TD]
[/TR]
[TR]
[TD]WO1386869[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Unpackaged[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[BrianJN1]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

I found a typo in my earlier Conditional Formatting formula change it to:
=AND(E3="Third",E3<>D3)
In my previous one I had E3<>E3!

Next in G3 enter this formula:
=IF(OR(D3="",E3=""),"Unpackaged",IF(D3=E3,"Packaged","UnPackaged"))

If it finds that either a D cell or an E cell is blank it will report "Unpackaged" in normal colour. If the D and E cells are the same we have "Packaged" but if E is "Third" we have "Unpackaged" reported in red.

 

[mlnet]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

I found a typo in my earlier Conditional Formatting formula change it to:
=AND(E3="Third",E3<>D3)
In my previous one I had E3<>E3!

Next in G3 enter this formula:
=IF(OR(D3="",E3=""),"Unpackaged",IF(D3=E3,"Packaged","UnPackaged"))

If it finds that either a D cell or an E cell is blank it will report "Unpackaged" in normal colour. If the D and E cells are the same we have "Packaged" but if E is "Third" we have "Unpackaged" reported in red.

Brian,

You are the BEST! Your formulas worked!

I am soooo grateful for your time and patience with my explanations and samples.

Thank you!!

 

[BrianJN1]

Re: Help with Conditional Formula to Highlight In Red a Change in Text

Knowing exactly what is require is always a big help.
Glad we have a satisfactory outcome, and thanks for your thoughts.