Multiple conditional formating for one cell
I have a workbook with several sheets. Sheet1 has several hundred rows of
text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork is only part of the words in each of the cell. Your help is appreciated in advance. -- tech1NJ |
Multiple conditional formating for one cell
tech1NJ
I would use a column (A say) on Sheet1 to search for your user's keyword in your text, row by row =IFERROR(FIND(special_char,B16),FALSE) (text here assumed to be in column B). Column B is then conditionally formatted according to the formula in $A16 etc. A number (string found) rates as TRUE for the test, not found gives an error that is then replaced by FALSE. Finally you can 'hide' column A by reducing its width to zero. If you have more keywords use extra columns to search one by one and combine the boolean results with an =OR(A16,B16,C16) to determine the conditional formatting to use. I hope this helps "tech1NJ" wrote: I have a workbook with several sheets. Sheet1 has several hundred rows of text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork is only part of the words in each of the cell. Your help is appreciated in advance. -- tech1NJ |
Multiple conditional formating for one cell
This does not work....Is there any other thoughts?
-- tech1NJ "pbart" wrote: tech1NJ I would use a column (A say) on Sheet1 to search for your user's keyword in your text, row by row =IFERROR(FIND(special_char,B16),FALSE) (text here assumed to be in column B). Column B is then conditionally formatted according to the formula in $A16 etc. A number (string found) rates as TRUE for the test, not found gives an error that is then replaced by FALSE. Finally you can 'hide' column A by reducing its width to zero. If you have more keywords use extra columns to search one by one and combine the boolean results with an =OR(A16,B16,C16) to determine the conditional formatting to use. I hope this helps "tech1NJ" wrote: I have a workbook with several sheets. Sheet1 has several hundred rows of text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork is only part of the words in each of the cell. Your help is appreciated in advance. -- tech1NJ |
All times are GMT +1. The time now is 08:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com