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:

WO ID	WO Flag	Package Counter
WO 54833	First	4
WO 54833	Second	4
WO 54833	Second	4
WO 54833	Second	4

<tbody>
</tbody>

 

[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)?

Column C	Column D	Column E	Column F	Column G
WO IDs	WO Code	Refreshed WO Code	Counter	Counter Status
WO1386866	First	First	6	Packaged
WO1386866	Second	Second	6	Packaged
WO1386866	Second	Second	6	Packaged
WO1386867	Second	Second	6	Packaged
WO1386868	Second	Second	6	Packaged
WO1386869				Packaged

<tbody>
</tbody>

 

[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".

Column C	Column D	Column E	Column F	Column G
WO IDs	WO Code	Refreshed WO Code	Counter	Counter Status
WO1386866	First	First	6	Packaged
WO1386866	Second	Second	6	Packaged
WO1386866	Second	Second	6	Packaged
WO1386867	Second	Third	6	Unpackaged
WO1386868	Second	Third	6	Unpackaged
WO1386869				Unpackaged

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[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.