Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2017, 04:23 PM
peterpiper peterpiper is offline Help with formula in Conditional Formating Windows 7 64bit Help with formula in Conditional Formating Office 2007
Novice
Help with formula in Conditional Formating
 
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 Help with formula in Conditional Formating Windows 7 64bit Help with formula in Conditional Formating Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
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
Reply With Quote
  #3  
Old 09-24-2017, 10:27 AM
peterpiper peterpiper is offline Help with formula in Conditional Formating Windows 7 64bit Help with formula in Conditional Formating Office 2007
Novice
Help with formula in Conditional Formating
 
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, 8 views)
Reply With Quote
  #4  
Old 09-24-2017, 11:54 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with formula in Conditional Formating Windows 7 64bit Help with formula in Conditional Formating Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with formula in Conditional Formating Conditional formating MJS16 Excel 16 12-01-2016 01:09 PM
Conditional Formating MaineLady Excel 2 10-30-2016 02:08 PM
Help with formula in Conditional Formating Conditional Formating Aseren Excel 14 02-18-2016 01:05 PM
Conditional Formating MaineLady Excel 1 11-05-2014 06:37 PM
Help with formula in Conditional Formating more Conditional Formating princeofsadness Excel 2 09-05-2011 02:14 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft