Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am currently using Excel 2000 to review and color code system logs (such as
backup logs). For example, I typically color code success entries as green, and error (failure) entries as red. I am aware of how to use conditional formatting at a basic level, and am aware that I can use the €śCell Value Is€ť €śequal to€ť function, but this seems to work only for text strings that equal the entire cell. This technique does not seem to work for portions of text within a cell (keywords). I would like to use conditional formatting as follows: * If cell contains the word(s) €śsuccess€ť or €ścomplete€ť, highlight in green. * If cell contains the word(s) €śerror€ť or €śfailure€ť, highlight in red. I know that I can use the €śFIND€ť or €śSEARCH€ť function in an adjacent cell to return TRUE if a word is present, and that I could then use conditional formatting to make that cell highlight in a green or red color. This technique seems to work if I implement conditional formatting on a cell-by-cell basis. I have not as yet found a single formula that allows me to use this technique across a range of cells because the €śFIND€ť or €śSEARCH€ť functions need to be explicitly pointed to a given cell. It is not efficient to implement this technique on a cell-by-cell basis €“ my log files may contain several thousand lines of information. Does anyone know of a method of effectively doing the following€¦ Select range = a1:a1000, implement conditional formatting such that if a cell contains one (or more) keywords (success or complete), highlight in green, and if the same cell contains one (or more) other keywords (error or failure), highlight in red? Any assistance is appreciated€¦ -- RichG |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Select your range of data (a1:a1000) so that A1 is the active cell.
Go to Conditional Formatting. Enter the Custom Formula: =OR(ISNUMBER(SEARCH("complete",A1)),ISNUMBER(SEARC H("success",A1))) Select your format (Green) Click ADD Enter the Custom Formula: =OR(ISNUMBER(SEARCH("error",A1)),ISNUMBER(SEARCH(" failure",A1))) Select your format (Red) Click OK That should do the trick. HTH, Elkar "RichG" wrote: I am currently using Excel 2000 to review and color code system logs (such as backup logs). For example, I typically color code success entries as green, and error (failure) entries as red. I am aware of how to use conditional formatting at a basic level, and am aware that I can use the €śCell Value Is€ť €śequal to€ť function, but this seems to work only for text strings that equal the entire cell. This technique does not seem to work for portions of text within a cell (keywords). I would like to use conditional formatting as follows: * If cell contains the word(s) €śsuccess€ť or €ścomplete€ť, highlight in green. * If cell contains the word(s) €śerror€ť or €śfailure€ť, highlight in red. I know that I can use the €śFIND€ť or €śSEARCH€ť function in an adjacent cell to return TRUE if a word is present, and that I could then use conditional formatting to make that cell highlight in a green or red color. This technique seems to work if I implement conditional formatting on a cell-by-cell basis. I have not as yet found a single formula that allows me to use this technique across a range of cells because the €śFIND€ť or €śSEARCH€ť functions need to be explicitly pointed to a given cell. It is not efficient to implement this technique on a cell-by-cell basis €“ my log files may contain several thousand lines of information. Does anyone know of a method of effectively doing the following€¦ Select range = a1:a1000, implement conditional formatting such that if a cell contains one (or more) keywords (success or complete), highlight in green, and if the same cell contains one (or more) other keywords (error or failure), highlight in red? Any assistance is appreciated€¦ -- RichG |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Nov 20, 6:59 am, RichG wrote:
I am currently using Excel 2000 to review and color code system logs (such as backup logs). For example, I typically color code success entries as green, and error (failure) entries as red. I am aware of how to use conditional formatting at a basic level, and am aware that I can use the "Cell Value Is" "equal to" function, but this seems to work only for text strings that equal the entire cell. This technique does not seem to work for portions of text within a cell (keywords). I would like to use conditional formatting as follows: * If cell contains the word(s) "success" or "complete", highlight in green. * If cell contains the word(s) "error" or "failure", highlight in red. I know that I can use the "FIND" or "SEARCH" function in an adjacent cell to return TRUE if a word is present, and that I could then use conditional formatting to make that cell highlight in a green or red color. This technique seems to work if I implement conditional formatting on a cell-by-cell basis. I have not as yet found a single formula that allows me to use this technique across a range of cells because the "FIND" or "SEARCH" functions need to be explicitly pointed to a given cell. It is not efficient to implement this technique on a cell-by-cell basis - my log files may contain several thousand lines of information. Does anyone know of a method of effectively doing the following... Select range = a1:a1000, implement conditional formatting such that if a cell contains one (or more) keywords (success or complete), highlight in green, and if the same cell contains one (or more) other keywords (error or failure), highlight in red? Any assistance is appreciated... -- RichG One way... For green Formula Is: =OR(NOT(ISERROR(FIND("success", $A1))),NOT(ISERROR(FIND("complete",$A1)))) For red Formula Is: =OR(NOT(ISERROR(FIND("error", $A1))),NOT(ISERROR(FIND("failure",$A1)))) Ken Johnson |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try this:
Select A1:A1000, with A1 as the active cell From the Excel Main Menu: <format<conditional formatting Condition_1: Formula is: =OR(COUNTIF($A1,"*success*"),COUNTIF($A1,"*complet e*")) Condition_2: Formula Is: =OR(COUNTIF($A1,"*error*"),COUNTIF($A1,"*failure*" )) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "RichG" wrote in message ... I am currently using Excel 2000 to review and color code system logs (such as backup logs). For example, I typically color code success entries as green, and error (failure) entries as red. I am aware of how to use conditional formatting at a basic level, and am aware that I can use the "Cell Value Is" "equal to" function, but this seems to work only for text strings that equal the entire cell. This technique does not seem to work for portions of text within a cell (keywords). I would like to use conditional formatting as follows: * If cell contains the word(s) "success" or "complete", highlight in green. * If cell contains the word(s) "error" or "failure", highlight in red. I know that I can use the "FIND" or "SEARCH" function in an adjacent cell to return TRUE if a word is present, and that I could then use conditional formatting to make that cell highlight in a green or red color. This technique seems to work if I implement conditional formatting on a cell-by-cell basis. I have not as yet found a single formula that allows me to use this technique across a range of cells because the "FIND" or "SEARCH" functions need to be explicitly pointed to a given cell. It is not efficient to implement this technique on a cell-by-cell basis - my log files may contain several thousand lines of information. Does anyone know of a method of effectively doing the following. Select range = a1:a1000, implement conditional formatting such that if a cell contains one (or more) keywords (success or complete), highlight in green, and if the same cell contains one (or more) other keywords (error or failure), highlight in red? Any assistance is appreciated. -- RichG |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Elkar:
That works perfectly - does just what I was looking for. Thank you VERY MUCH!!! -- RichG "Elkar" wrote: Select your range of data (a1:a1000) so that A1 is the active cell. Go to Conditional Formatting. Enter the Custom Formula: =OR(ISNUMBER(SEARCH("complete",A1)),ISNUMBER(SEARC H("success",A1))) Select your format (Green) Click ADD Enter the Custom Formula: =OR(ISNUMBER(SEARCH("error",A1)),ISNUMBER(SEARCH(" failure",A1))) Select your format (Red) Click OK That should do the trick. HTH, Elkar "RichG" wrote: I am currently using Excel 2000 to review and color code system logs (such as backup logs). For example, I typically color code success entries as green, and error (failure) entries as red. I am aware of how to use conditional formatting at a basic level, and am aware that I can use the €śCell Value Is€ť €śequal to€ť function, but this seems to work only for text strings that equal the entire cell. This technique does not seem to work for portions of text within a cell (keywords). I would like to use conditional formatting as follows: * If cell contains the word(s) €śsuccess€ť or €ścomplete€ť, highlight in green. * If cell contains the word(s) €śerror€ť or €śfailure€ť, highlight in red. I know that I can use the €śFIND€ť or €śSEARCH€ť function in an adjacent cell to return TRUE if a word is present, and that I could then use conditional formatting to make that cell highlight in a green or red color. This technique seems to work if I implement conditional formatting on a cell-by-cell basis. I have not as yet found a single formula that allows me to use this technique across a range of cells because the €śFIND€ť or €śSEARCH€ť functions need to be explicitly pointed to a given cell. It is not efficient to implement this technique on a cell-by-cell basis €“ my log files may contain several thousand lines of information. Does anyone know of a method of effectively doing the following€¦ Select range = a1:a1000, implement conditional formatting such that if a cell contains one (or more) keywords (success or complete), highlight in green, and if the same cell contains one (or more) other keywords (error or failure), highlight in red? Any assistance is appreciated€¦ -- RichG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formatting Certain Words | Excel Discussion (Misc queries) | |||
conditional formatting for specific words in cell | Excel Discussion (Misc queries) | |||
conditional formatting -- testing for multiple words | Excel Discussion (Misc queries) | |||
conditional formatting on words | Excel Discussion (Misc queries) | |||
conditional formatting for cells containing words | Excel Discussion (Misc queries) |