Conditional formatting by key words

[Guest]

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…

 

[Guest]

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(SEARCH("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

 

[Ken Johnson]

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...

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

 

[Ron Coderre]

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,"*complete*"))

Condition_2:
Formula Is: =OR(COUNTIF($A1,"*error*"),COUNTIF($A1,"*failure*"))


Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Elkar:

That works perfectly - does just what I was looking for.

Thank you VERY MUCH!!!