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>

 

[BrianJN1]

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

I hope I'm not misunderstanding but this seems like you just need to highlight the column, go to Conditional formatting, find the Equals tag and enter the formula: ="third" and then choose your red text option.

 

[mlnet]

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

Hi Brian,

Thank you so much for responding to my issue.

I think it is me not explaining my issue correctly.

I have 100 WO IDs that have multiple flags with the sequence, First and Second. I also have single items that have the flag third and those are fine. What I need to flag are the items under a particular WO ID that changes from Second to Third. So, If I have 4 items counted for WO 54833 and the Flags are First and Second, and then the next day when I refresh the data and one of the flags turns to “Third”, then I need to flag it as red.

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

<tbody>
</tbody>

Update WO ID:

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

<tbody>
</tbody>

 

[BrianJN1]

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

You actually have two "products" to which you can assign the value "third"? One having an hierarchical value and the other being automatically assigned that top value?

Are you able to easily change the value of the other to something like 3rd or three? Third won't do as Conditional formatting sees third and Third as the same.

 

[mlnet]

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

Oh boy! I see what you mean Brian. Unfortunately, I am refreshing the data from a master database so there is no way to update the field or data. I just need to find a way to flag in color when any data in the WO Flag column changes.

 

[BrianJN1]

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

Hm ...
Can you introduce a new column for "today"?

WO ID	WO Flag	Today	Package Counter
WO 54833	First	Second	4
WO 54834	Second	Second	4
WO 54835	Second	Second	4
WO 54836	Second	Third	4

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>

The Conditional formatting rule for Column C (in my sheet) reads as: =AND(C3="Third",C3<>B3)
Note that if First changes to Second (first entry) there is no change but at the last entry we have RED.

Is that a possibility? I can't think of anything further.

 

[mlnet]

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

Thank you, I will try anything and this sounds like a great suggestion.

In the conditional formatting tool, do I place the formula using the following “New Rule”?

New Rule
Use a formula to determine which cells to form
Format values where this formula is true:
=AND(C3="Third",C3<>B3)

 

[BrianJN1]

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

Yes, you can use "New Rule" and then select "Use a formula to determine ..."

Let us know how that works.

 

[mlnet]

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

Hi Brian,

So, I tried the =AND(C3="Third",C3<>B3) formula and I could not get the conditional formula to work consistently. It would work initially, but if I filtered on Column C, the conditional formula would not work. Frustrating.

I like your idea about adding an additional column with the Refreshed or Today data. So, I added the 2^nd column (Refreshed WO Code Refreshed WO Code). To verify any updates to the WO Code, I added the formula =D3=E3 in column G. So, if the data in column E changes (See Table 2) the Counter Status will change from True to False. So far, so good. Now how can I modify the formula to populate “Packaged” instead of TRUE and “Unpackaged” instead of FALSE?

TABLE 1:

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

<tbody>
</tbody>

TABLE 2:

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

<tbody>
</tbody>

<tbody>
</tbody>

 

[BrianJN1]

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

Hi Brian,

So, I tried the =AND(C3="Third",C3<>B3) formula and I could not get the conditional formula to work consistently. It would work initially, but if I filtered on Column C, the conditional formula would not work. Frustrating.

TABLE 1:

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

<tbody>
</tbody>

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.