Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Conditional formatting by key words

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Conditional formatting by key words

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,073
Default Conditional formatting by key words

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Conditional formatting by key words

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Conditional formatting by key words

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Certain Words jaxstraww Excel Discussion (Misc queries) 1 May 16th 07 06:51 PM
conditional formatting for specific words in cell MW Excel Discussion (Misc queries) 3 August 28th 06 08:28 PM
conditional formatting -- testing for multiple words The Moose Excel Discussion (Misc queries) 8 August 20th 06 02:11 PM
conditional formatting on words [email protected] Excel Discussion (Misc queries) 11 June 5th 06 06:56 PM
conditional formatting for cells containing words LawW Excel Discussion (Misc queries) 2 March 21st 06 07:43 PM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"