Conditional Formatting

[kjlorfeld]

I'm trying to format rows containing the word total in one of the first three columns. The problem is "Total" is preceeded by various other words throughout the spreadsheet.

One thought I had was inserting a column with if statements returning one value if "Total" appeared, and another if it didn't, but wasn't able to get the if statement to work correctly due to the other words before total.

Any thoughts?

 

[colbymack]

If you are just trying to test for the existence of Total or total, then you could use the search command. This returns #value if the search string is not found, or it returns the character position in the string where the search text starts.

=SEARCH("total",A1)

This will search A1 for the presence of the word total and return either a numeric value, or the #value error. You can put this in an if statement to do something if it finds a value vs if it finds the #value error. Such as:

=IF(ISERROR((SEARCH("total",A1))),"ERROR","FOUND")

HTH
Colbymack

 

[colbymack]

I forgot to mention - you can then do your conditional formatting based on whether the value returned is greater than or equal to 1. The #value error does not meet this criteria.

colbymack

 

[kjlorfeld]

This works.

=IF(ISERROR((SEARCH("total",D72))),(IF(ISERROR((SEARCH("total",E72))),IF(ISERROR((SEARCH("total",F72))),"error","found"),"found")),"found")

Now I have "found" or "error" in column A. If found appears, I want the entire row to be bold. Any thoughts on this?

 

[colbymack]

OK - now that I have taken you down that path, I am going to rescind and give you a different solution, using some similar functions, but not nearly the same.

Highlight the whole spreadsheet using the little square to the left of the A column and above the list of row numbers (click on that square). Alternatively, use the CTRL+A keyboard shortcut.

Select the Format menu. Select conditional formatting.

Change the drop down box from "Cell Value Is" to "Formula Is"
Enter this formula: =ISNUMBER(SEARCH("total",$D1))
Click on the Format button. Click on the patterns tab, and select the color that you want the row to become. Click OK.
Click on the Add button, and repeat the above steps from "Change the drop . . . " on to this step - do so twice, but changing the formula to $E1 and $F1.

Basically this evaluates D, E, and F for the word total, and if found, it returns a True value for the ISNUMBER statement. This then kicks the conditional formatting on for that row.

Does this make sense?

HTH,
Colbymack

 

[colbymack]

that was a fantastic thought on my part "similar, but not nearly the same" . . .

 

[kjlorfeld]

That worked! Thank you SO much - you made life quite a bit easier for me!

 

[colbymack]

Glad to help. Sorry I took you on a different tangent to start off with . . .

colbymack

 

[barry houdini]

Within conditional formatting you don't really need the ISNUMBER part, you can use
=SEARCH("total",$D1)

If you want to exclude matches with other words, such as "totals" or "totalitarian" (possibly not very likely :wink: ) then this formula might help

=SEARCH(" total "," "&$D1&" ")

 

[Peter_SSs]

What about this way for checking in all three columns at once rather than using three conditions?

1. Select D1:F6
2. Format|Conditional Formatting...|Formula is: =SEARCH("total",$D1&$E1&$F1)
3. Set colour, bold etc

Mr Excel.xls
	D	E	F	G
1	what total?	other text	other text
2	other text	other text	other text
3	other text	total is	another total here
4	other text	other text	other text
5	other text	other text	other text
6	other text	other text	total
7
Conditional Format Total