| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting with asterisk
Hi all,
How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
Try
=ISNUMBER(FIND("*",A3)) Regards, Alan. "Jack Sons" wrote in message ... Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
Put this conditional format in A3 after selecting Formula is
=FIND("*",A3)0 Then use the format painter to paint the format into your range of cells. Mike "Jack Sons" wrote: Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
How about:
=ISNUMBER(SEARCH("~*",$A$3)) You really want those $'s? Jack Sons wrote: Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
And you don't even need the =isnumber() bit.
=SEARCH("~*",$A$3) An error will be treated as false. Dave Peterson wrote: How about: =ISNUMBER(SEARCH("~*",$A$3)) You really want those $'s? Jack Sons wrote: Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
=COUNTIF($a$3,"*~**")
You have to account for the characters before and after -- Regards, Tom Ogilvy "Jack Sons" wrote: Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting with asterisk
Gentlemen,
Thanks for your help (fast as lightning) and the explanation. Jack. "Jack Sons" schreef in bericht ... Hi all, How to do the conditional formatting if I want in A3:P200 the back ground color of a cell to be yellow if it contains an asterisk. Example, cells may look like ABC/D*EF/JSO/HIJ in which case I want the back ground yellow. I tried a lot, but (for instance) =countif($A$3="~*" ) etc. won't work. How to make clear that with * is meant just that character, not the wildcard? TIA Jack Sons The Netherlands -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! -------------------------------------------------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 102 spam-mails zijn er tot op heden geblokkeerd. Download de gratis SPAMfighter vandaag nog! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| asterisk | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| asterisk in formulas | Excel Discussion (Misc queries) | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
