Conditional Formula to Highlight In Red a Change in Text

mlnet

New Member
Joined
Sep 12, 2008
Messages
45
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]
 
Last edited:
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]
 
Last edited:
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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!! :-D
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top