Forum Discussion
Conditional Formatting a specific word
First post here, so hoping someone can help me. I work for a repair facility. I track our repairs in excel. I have an “order” tab that tracks all the important information for the repairs. I also have an “inventory” tab that shows what parts we have in stock in our inventory. Also on this tab I do a daily data dump of all the parts needed for each order. I use this dump to fill in a “parts needed” column on the repair tab to show what specific parts, based on part name, an order needs to be completed.
Is there a way for me to condition the “Parts Needed” cells on the repair tab to highlight the part name if the part shows in stock on the inventory tab?
I.E -
Repair tab:
order 0001 for Jane Doe
Started work 1/23/45
needs parts: mouse, keyboard, monitor, hard drive
Inventory Tab:
Daily parts dump:
order 0001 - part needed: mouse
order 0001 - part needed: keyboard
order 0001 - part needed: monitor
order 0001 - part needed: hard drive
In stock report:
Mouse 10
Keyboard 0
Monitor 0
Hard drive 5
3 Replies
Select cells you want in column A and in Conditional Formatting apply below formula:
=--SUBSTITUTE(LOWER($B2:$B10), TEXTAFTER($A2:$A10, ": "), "") > 0Format cells with color you want.
Change the range in formula as per you need.
Hope this helps.
IlirU
Yes you can. I think I created what you want but it is hard to know exactly how you have things set up without the sheet. Here is the formula I created:
=SUM(IF(ISNUMBER(SEARCH(TEXTAFTER(A1,": "),$B$1:.$B$9999)),--TRIM(TEXTAFTER(LOWER($B$1:.$B$9999),LOWER(TEXTAFTER(A1,": ")),,,,0)),0))based on the sheet looking like this:
and that formula is put into the conditional formatting under a custom rule applied to column A1 (either A:A or A1:Axxx where xxx is the whole range of A that you want it applied to)
That rule will look at each cell in A and check for the value after the ": " to be found in column B and if found then find the VALUE after that keyword and if it finds any value > 0 then it will highlight that cell according to the conditional formatting (in this case green).
In another post I will try to attach the sample file because this forum sometimes deletes posts with attachments....
and here I will try to attach the sample file
