Microsoft Office Forums Conditional Formatting

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2019, 02:57 AM
Paul_ Paul_ is offline Conditional Formatting Windows 10 Conditional Formatting Office 2019
Novice
Conditional Formatting
 
Join Date: May 2019
Posts: 2
Paul_ is on a distinguished road
Default Conditional Formatting

I have put together a simple spreadsheet and in one section i want to highlight values in four columns by filling the cell with colour to determine if the calculated value in either of those columns are higher or lower than a value in another reference column by changing the cell colour to red (higher) or Green (lower).



I have written the conditional formatting okay and it works on the first row of the sheet but when I use format painter to copy it to other cells the cell references and colour do not change their references they are still referencing the first cell in column S rather than the associated row.

For example row 5 cell T5, U5, Z5, AA5 would still reference Cell S4 rather than S5.
Attached Files
File Type: xlsm Windows dimensions 190429 BA.xlsm (30.5 KB, 1 views)
Reply With Quote
  #2  
Old 05-09-2019, 03:35 AM
ArviLaanemets ArviLaanemets is offline Conditional Formatting Windows 8 Conditional Formatting Office 2016
Expert
 
Join Date: May 2017
Posts: 455
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Use PasteSpecial.ConditionalFormats. Or select the whole range you want to apply the conditional format to at start. And be sure you have absolute and relative reverences set properly (defining conditional formatting, the formula is written for topmost selected row, and the rule is copied down by excel like ordinary formula). Usually having row reference relative is enough.
Reply With Quote
  #3  
Old 05-09-2019, 03:45 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
I adapted it for column T, same method applies to other columns
You have to use a relative reference for the rows like $T4 not $T$4
You should also apply to the entire range
Perhaps adapt your formula for error values in col S like
=ISERROR($t4) and format as required
Attached Files
File Type: xlsm Windows dimensions 190429 BA.xlsm (30.9 KB, 4 views)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 05-10-2019, 02:53 AM
Paul_ Paul_ is offline Conditional Formatting Windows 10 Conditional Formatting Office 2019
Novice
Conditional Formatting
 
Join Date: May 2019
Posts: 2
Paul_ is on a distinguished road
Default conditional formatting

Quote:
Originally Posted by Pecoflyer View Post
Hi and welcome
I adapted it for column T, same method applies to other columns
You have to use a relative reference for the rows like $T4 not $T$4
You should also apply to the entire range
Perhaps adapt your formula for error values in col S like
=ISERROR($t4) and format as required
Thanks for your help it worked fine.
Regards

Paul
Reply With Quote
  #5  
Old 05-10-2019, 05:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting Windows 7 64bit Conditional Formatting Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

You're welcome
In the future would you please avoid quoting entire posts? They are just clutter.
Use Quick Reply instead. Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
Reply

Tags
conditional formatting

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Conditional Formatting Joanne Excel 8 01-28-2019 08:30 AM
Conditional Formatting Conditional Formatting solberg2004 Excel 2 01-27-2017 10:07 AM
Help with if and conditional formatting piper7971 Excel 11 06-10-2015 12:07 AM
Conditional Formatting Conditional formatting technofrank Excel 3 06-08-2015 03:15 AM
Conditional Formatting Conditional formatting that ignores other formatting rules info_guy2 Excel 1 07-03-2014 10:07 AM


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


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