Conditional Formula to Highlight In Red a Change in Text

M

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:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.
 
Upvote 0
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.


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

Update WO ID:
[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]Third
[/TD]
[TD] 4
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.
 
Upvote 0
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. :(
 
Upvote 0
Re: Help with Conditional Formula to Highlight In Red a Change in Text

Hm ...
Can you introduce a new column for "today"?
[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="width: 64"]WO ID[/TD]
[TD="class: xl65, width: 64"]WO
Flag[/TD]
[TD="class: xl65, width: 64"]Today
[/TD]
[TD="class: xl65, width: 64"]Package
Counter[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WO
54833[/TD]
[TD]First[/TD]
[TD]Second[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WO
54834[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WO
54835[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WO
54836[/TD]
[TD]Second[/TD]
[TD]Third[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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 2nd 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:
[TABLE="width: 424"]
<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]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386867
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386868
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386869
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]


TABLE 2:
[TABLE="width: 424"]
<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]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386866
[/TD]
[TD]Second
[/TD]
[TD]Second
[/TD]
[TD]6
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386867
[/TD]
[TD]Second
[/TD]
[TD]Third
[/TD]
[TD]6
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]WO1386868
[/TD]
[TD]Second
[/TD]
[TD]Third
[/TD]
[TD]6
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]WO1386869
[/TD]
[TD]Second
[/TD]
[TD]Third
[/TD]
[TD]6
[/TD]
[TD]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 424"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Re: Help with Conditional Formula to Highlight In Red a Change in Text

mlnet said:
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:
[TABLE="width: 424"]
<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]TRUE
[/TD]
[/TR]
[TR]
[TD]WO1386866[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]WO1386866[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]WO1386867[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]WO1386868[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]WO1386869[/TD]
[TD]Second[/TD]
[TD]Second[/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Conditional Format - Highlight Cell Based on Date
2
Replies
10
Views
145
DanteAmor
DanteAmor
S
  • Solved
Conditional format to highlight future dates
Replies
3
Views
238
Joe4
Joe4
Y
  • Solved
Need formula to match
2
Replies
10
Views
596
yukonexcel
Y
C
  • Question Question
How do I highlight a cell where yesterday's date has passed?
Replies
5
Views
629
AlanY
A
W
  • Solved
Highlighting cell contents, based on a range
2
Replies
11
Views
471
felixstraube
F

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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