#1
|
|||
|
|||
Help with formula in Conditional Formating
Hello all
I am trying to use conditional formatting to change the colour of a row when text reads "XXXX", easy i know and found how to do it using formulas however i've come across a problem i cant solve myself. I have a column with text which states text and date for example SENT 1/1/12. When i use the formula =&A1="SENT" it wont change the colour due to the date being in the same cell. I need the formula to search the cell and change colour if it contains the text SENT but not exclusively. SO the cell could have the text SENT TODAY or SENT YESTERDAY and still work. I know CF does this with its own- rules cell value contains "XX" - but it wont highlight the entire row when i try this. I need the entire row to change and not be exclusive for the text i select. Hope this all makes sense. Thanks in advance |
#2
|
||||
|
||||
Hi
supposing your range is A1:X100, and the column with text is col A, select the entire range, open the CF wizard and enter =ISNUMBER(SEARCH("SENT",$a1)) as formula.Format as needed - Exit the wizard - Done ( if you need a case sensitive function use FIND instead) Adapt range and cell ref to your needs keeping in mind that the place of the $ sign is crucial
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi
I've tried the find and search in the formula as you suggested and its come up with some random highlighted lines. I've attached a example file please take a look, for some reason it wont highlight rows 4 or 7? Thanks |
#4
|
||||
|
||||
That is because your condition is not correct
You entered = search("test",$E1) for a range starting on row 2 Change $E1 to $E2 and you're ok
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formating | MJS16 | Excel | 16 | 12-01-2016 01:09 PM |
Conditional Formating | MaineLady | Excel | 2 | 10-30-2016 02:08 PM |
Conditional Formating | Aseren | Excel | 14 | 02-18-2016 01:05 PM |
Conditional Formating | MaineLady | Excel | 1 | 11-05-2014 06:37 PM |
more Conditional Formating | princeofsadness | Excel | 2 | 09-05-2011 02:14 AM |