Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2015, 08:30 AM
grexcelman grexcelman is offline Conditional Formatting Windows 7 32bit Conditional Formatting Office 2003
Novice
Conditional Formatting
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Question Conditional Formatting

I'm trying to run a macro that helps me analyze monthly trend data. Ideally, the macro would highlight all cells where its respective absolute deviation is greater than the average deviation for its monthly trend(its row of data.) I already calculate the trend's average deviation for the monthly trend in a separate column and this formula has been copied down several rows. Any help is appreciated. I can post a sample spreadsheet if this wasn't clear. Thanks in advance!
Reply With Quote
  #2  
Old 01-09-2015, 09:35 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,418
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Do you need a macro or would regular CF do?
Reply With Quote
  #3  
Old 01-09-2015, 09:41 AM
grexcelman grexcelman is offline Conditional Formatting Windows 7 32bit Conditional Formatting Office 2003
Novice
Conditional Formatting
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

That's a good question. I have about 14 sheets I need to apply this formatting to and I need to sometimes be able to turn on/off the formatting for presenation purposes. Figured a macro allowing me to activate the formatting would be best but I'm open to suggestions. The other issue I had was with applying this formula relatively from cell to cell. The formula rule seem to stay the same through the range of data. I may not be using the CF menu correctly.
Reply With Quote
  #4  
Old 01-09-2015, 10:21 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,418
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

If you need to turn it off once in a while, a macro would definitely help. I'm sorry I'm not at all proficient with macros.

As for the usual CF you would use something like =a1:a10<$f$20 where f20 contains the comparison value. Note that the range is relative
Reply With Quote
  #5  
Old 01-12-2015, 09:26 AM
grexcelman grexcelman is offline Conditional Formatting Windows 7 32bit Conditional Formatting Office 2003
Novice
Conditional Formatting
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

Trying the regular conditional format route but that isn't working. Even adjusting for the cell locks, the fomatting formula rule remains the same when I copy the format over into each cell in the range. Why does it do that? My rule is: =ABS(C4-$Q4)>$R4
every cell in the range reads this way and for example, in cell D9, it should read: =abs(D9-$Q9)>$R9 but it remains the same as the original. is this a limitation of CF or have set the function incorrectly? I figure I could always turn this into a macro by recording the steps but i can't even get this to work correctly on its own.
Reply With Quote
  #6  
Old 01-12-2015, 09:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,418
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Perhaps post a small sample sheet ( no pics) so we can see what goes wrong
Reply With Quote
  #7  
Old 01-12-2015, 09:42 AM
grexcelman grexcelman is offline Conditional Formatting Windows 7 32bit Conditional Formatting Office 2003
Novice
Conditional Formatting
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

redacted version attached.
Attached Files
File Type: xlsx sample_trend.xlsx (10.8 KB, 1 views)
Reply With Quote
  #8  
Old 01-12-2015, 09:45 AM
grexcelman grexcelman is offline Conditional Formatting Windows 7 32bit Conditional Formatting Office 2003
Novice
Conditional Formatting
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

maybe it's just a limitation with relative references and conditiona formatting. any other ideas on how to achieve what i'm looking to do and get around this issue? the alternative is to create this rule cell by cell?
Reply With Quote
  #9  
Old 01-12-2015, 10:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,418
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

I don't see anything wrong.The references of the CF don't change when you select a cell because the CF applies to the entire range and not only to the cell you selected
Yoy can see for yourself that the formatting is correct by entering your CF formula in a cell and dragging right and down. There you will see the formula adapt,and the TRUE values correspond to coloured cells.
It is also so that any formula entered in CF is considered an array formula by XL, and that is how they behave.

In conclusion, your solution is perfectly valid
Reply With Quote
Reply

Tags
conditional formatting, loop, macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting sdfrance Excel 3 01-06-2015 07:10 AM
Conditional Formatting Conditional formatting that ignores other formatting rules info_guy2 Excel 1 07-03-2014 10:07 AM
Conditional Formatting Conditional Formatting. Laurie B. Excel 6 04-09-2012 05:01 PM
Conditional Formatting Conditional formatting with AND, OR Lucky Excel 2 10-03-2011 11:41 PM
Conditional Formatting Conditional Formatting namedujour Excel 3 08-25-2011 01:46 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:09 PM.


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