Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-23-2017, 04:23 PM
peterpiper peterpiper is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jun 2017
Posts: 4
peterpiper is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-23-2017, 11:50 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,940
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

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
Reply With Quote
  #3  
Old 09-24-2017, 10:27 AM
peterpiper peterpiper is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jun 2017
Posts: 4
peterpiper is on a distinguished road
Default

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
Attached Files
File Type: xlsx testing.xlsx (10.5 KB, 1 views)
Reply With Quote
  #4  
Old 09-24-2017, 11:54 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,940
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

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
Reply With Quote
Reply

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


All times are GMT -7. The time now is 08:24 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft