Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Is it possible to do a partial word match for conditional formatting.
I want to search for any cell that contains the word 'listen' in either upper or lower case and the cell may contain other characters. I tried = *listen* but it didn't work. If possible, I'd also like to change the color of the whole row not just the matching cell in the row. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Try using the Formula Is option and use this formula:
=SEARCH("listen",cell_ref) If you want to highlight the entire row make the cell_ref absolute: =SEARCH("listen",$A1) -- Biff Microsoft Excel MVP "Maureen" wrote in message ... Is it possible to do a partial word match for conditional formatting. I want to search for any cell that contains the word 'listen' in either upper or lower case and the cell may contain other characters. I tried = *listen* but it didn't work. If possible, I'd also like to change the color of the whole row not just the matching cell in the row. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
I found a partial fix for my problem. Rather than conditional formatting, I
did a find/replace(under Edit-Replace on menu). Under 'Find What', I entered 'listen'. Under 'Replace With', I left the box empty but clicked on the format button where you can change the font or cell (pattern) color. That enabled me to change the cell. If possibly, I'd like to change the color of the whole row not just the cell. "Maureen" wrote: Is it possible to do a partial word match for conditional formatting. I want to search for any cell that contains the word 'listen' in either upper or lower case and the cell may contain other characters. I tried = *listen* but it didn't work. If possible, I'd also like to change the color of the whole row not just the matching cell in the row. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
When setting up conditional formatting select "Formula Is" then enter this
formula: =SEARCH("listen",A1)0 The cell I referenced (A1) is the same cell that is being formatted. You could affect the whole row by putting this conditional formating in the whole row but always reference cell (A1) or whatever cell you decide is the key. Hope this works for you. Tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
So, I'm not quite sure how to make that work on the whole worksheet or format
the whole row. I can add another column that uses =SEARCH("listen",A1)0, copy the formula all the way down the worksheet, then do a conditional format for the value "TRUE" but it only applies the conditional format color on the cell containing the word 'TRUE' not the whole row. If it try to do conditional format on a range ie. =SEARCH("listen",A1:I5000)0, I get odd results. Column I is the column that may contain the word 'listen'. If it finds 'listen', I want the row A15:I15 to be highlighted. "TomPl" wrote: When setting up conditional formatting select "Formula Is" then enter this formula: =SEARCH("listen",A1)0 The cell I referenced (A1) is the same cell that is being formatted. You could affect the whole row by putting this conditional formating in the whole row but always reference cell (A1) or whatever cell you decide is the key. Hope this works for you. Tom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Try this:
Select cell A1. From the menu select <Format<Conditional Formatting Change drop down to "Formula Is" Enter this formula =SEARCH("listen",$A$1)0 Select the format you want Select OK Test this by typeing "listen" in cell A1 to make sure you get your desired format. Next: Select cell A1. Copy Select all the cells you want to change format when A1 contains "listen" Paste Speccial, Formats All of the cells you pasted that format to should now change format based on the content of cell A1. I hope that is clear. Communication can be challenging. Tom |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Thanks, that gives same effect as find/replace with format - highlights
matching cell as expected. I would guess there isn't a way to highlight a whole row based on a match in 1 column. "TomPl" wrote: Try this: Select cell A1. From the menu select <Format<Conditional Formatting Change drop down to "Formula Is" Enter this formula =SEARCH("listen",$A$1)0 Select the format you want Select OK Test this by typeing "listen" in cell A1 to make sure you get your desired format. Next: Select cell A1. Copy Select all the cells you want to change format when A1 contains "listen" Paste Speccial, Formats All of the cells you pasted that format to should now change format based on the content of cell A1. I hope that is clear. Communication can be challenging. Tom |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Try this...
Assume you want to highlight rows where any cell in the range A1:I10 contains the word "listen"... Select the range A1:I10 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =COUNTIF($A1:$I1,"*listen*") Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Maureen" wrote in message ... Thanks, that gives same effect as find/replace with format - highlights matching cell as expected. I would guess there isn't a way to highlight a whole row based on a match in 1 column. "TomPl" wrote: Try this: Select cell A1. From the menu select <Format<Conditional Formatting Change drop down to "Formula Is" Enter this formula =SEARCH("listen",$A$1)0 Select the format you want Select OK Test this by typeing "listen" in cell A1 to make sure you get your desired format. Next: Select cell A1. Copy Select all the cells you want to change format when A1 contains "listen" Paste Speccial, Formats All of the cells you pasted that format to should now change format based on the content of cell A1. I hope that is clear. Communication can be challenging. Tom |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
You could paste the format to the whole row and the whole row would be
highlighted. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
partial word match for conditional formatting
Let's tweak Tom's steps a little:
Select range A1:G1. From the menu select <Format<Conditional Formatting Change drop down to "Formula Is" Enter this formula =SEARCH("listen",$A1)0 Select the format you want Select OK --note my changes in line 1 and 4. Next: Select range A1:G1. Copy Select all the cells you want to change format when Column A contains "listen" (so maybe A1:G300) Paste Speccial, Formats --note my changes in line 1 and 3. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
Conditional Formatting to match cell values | Excel Worksheet Functions | |||
DSUM Partial Match | Excel Worksheet Functions | |||
partial lookup/match | Excel Worksheet Functions | |||
Complicated conditional formatting if numbers match across a data set..How? | Excel Discussion (Misc queries) |