| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting help required
I am trying to format the background color of a cell based on text in that
cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
OK, so I figured out that the Search( ) function returns #Value! if it
doesn't find a the search string in the string to be searched (why can't it return a zero like the INSTR function in ACCESS? Thank you Microsoft!) So how do I work around this problem? -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
If the cell contains "PARTIAL", "GOOD", or "MEDIUM",
Are those words a *substring* or are they the only string? If they're the only string: =OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM") If those words are a *substring*: =OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium"))) Biff "Dale Fye" wrote in message ... I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))
Ooops! The cell references should all be D4. Biff "T. Valko" wrote in message ... If the cell contains "PARTIAL", "GOOD", or "MEDIUM", Are those words a *substring* or are they the only string? If they're the only string: =OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM") If those words are a *substring*: =OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium"))) Biff "Dale Fye" wrote in message ... I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
Thanks, Biff.
Substring (could be several differnet values). Isn't it a pitty we cannot standardize functions across VBA so that INSTR( ) from Access would work in Excel? I'll give that a try. -- Email address is not valid. Please reply to newsgroup only. "T. Valko" wrote: If the cell contains "PARTIAL", "GOOD", or "MEDIUM", Are those words a *substring* or are they the only string? If they're the only string: =OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM") If those words are a *substring*: =OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium"))) Biff "Dale Fye" wrote in message ... I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
If you have a whole bunch of words to check it would probably be easier to
list those words in a range of cells then use this more compact formula: G1:G10 = list of words =OR(ISNUMBER(SEARCH(G1:G10,D4))) Biff "Dale Fye" wrote in message ... Thanks, Biff. Substring (could be several differnet values). Isn't it a pitty we cannot standardize functions across VBA so that INSTR( ) from Access would work in Excel? I'll give that a try. -- Email address is not valid. Please reply to newsgroup only. "T. Valko" wrote: If the cell contains "PARTIAL", "GOOD", or "MEDIUM", Are those words a *substring* or are they the only string? If they're the only string: =OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM") If those words are a *substring*: =OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium"))) Biff "Dale Fye" wrote in message ... I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting help required
You could define your own function to make use of INSTR in VBA -
however, a UDF is likely to take longer to evaluate a result than using worksheet functions directly (depending on how complex they are). Pete On Mar 30, 7:14 pm, Dale Fye wrote: Thanks, Biff. Substring (could be several differnet values). Isn't it a pitty we cannot standardize functions across VBA so that INSTR( ) from Access would work in Excel? I'll give that a try. -- Email address is not valid. Please reply to newsgroup only. "T. Valko" wrote: If the cell contains "PARTIAL", "GOOD", or "MEDIUM", Are those words a *substring* or are they the only string? If they're the only string: =OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM") If those words are a *substring*: =OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEA*RCH(F3,"medium"))) Biff "Dale Fye" wrote in message ... I am trying to format the background color of a cell based on text in that cell. If the cell contains "EXCELLENT", I want to color the background green (works great). If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it yellow. I've tried putting the following in a Cell, but it just returns a #Value error. =OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0) When I only put one of these critiera in the OR function, it returns the proper logical value. Any ideas what I am doing wrong? Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4) individually as the cell forumla and they each returned a #Value! result. Any ideas how to make this work? -- Email address is not valid. Please reply to newsgroup only.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formula help required | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
| Conditional formatting help required please | Excel Discussion (Misc queries) | |||
| conditional value required on basis of column a | Excel Worksheet Functions | |||
Linear Mode
