Microsoft Office Forums Conditional Formatting that highlights cells when there was a change in another column

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2019, 06:27 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formatting that highlights cells when there was a change in another column Windows 7 32bit Conditional Formatting that highlights cells when there was a change in another column Office 2013
Competent Performer
Conditional Formatting that highlights cells when there was a change in another column
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default Conditional Formatting that highlights cells when there was a change in another column

Hi, my problem is related to Lolla70's previous post that was moved to the programming section. In the profiling of households, the members (Column A) are categorized into groups (Column D) according to their age (Column C). I need help in:


1. A conditional formatting formula that will highlight the name of a member whose group status (Column D) was changed in the last quarter or 90 days. The profile date is entered in $B11.

2. Is there a formula that will calculate the age group (Column D) without Column C?




Thank you.
Attached Files
File Type: xlsx Class by Age.xlsx (83.6 KB, 2 views)
Reply With Quote
  #2  
Old 04-26-2019, 08:19 AM
p45cal p45cal is offline Conditional Formatting that highlights cells when there was a change in another column Windows 10 Conditional Formatting that highlights cells when there was a change in another column Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by Marcia View Post
1. A conditional formatting formula that will highlight the name of a member whose group status (Column D) was changed in the last quarter or 90 days. The profile date is entered in $B11.
Not sure what you want here; something that's changed in the last 90 days is comparing the status today with the status 90 days ago. Does your profile date have any relevance at all?
Perhaps you want to compare the profile date with 90 days ago?, or the profile date with 90 days before the profile date?


So I can't give you a defintive answer, but I can get you started. One way is to calculate the classification code twice, once for one date and once for the other, compare the result. If they're the same, no change, if they're different then of course it has changed between those 2 dates.
Conditional formatting wants to see a TRUE/FALSE result for it to decide whether to highlight or not. I've added a column to your lower table which uses two dates: your ProfileDate and a new ComparisonDate (cell D11). So you can experiment adjusting the two dates and watching column E. TRUE means it's changed, FALSE not. The formula in that column can ultimately be the basis of the formula you end up using in conditional formatting - I wouldn't expect that you will need to have column E at all ultimately.


Quote:
Originally Posted by Marcia View Post
2. Is there a formula that will calculate the age group (Column D) without Column C?
yes:
=VLOOKUP(DATEDIF([@Birthday],ProfileDate,"d"),tAgeGroup,4)
Attached Files
File Type: xlsx msofficeforums140784Class by Age.xlsx (84.6 KB, 3 views)
Reply With Quote
  #3  
Old 04-28-2019, 01:14 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formatting that highlights cells when there was a change in another column Windows 7 32bit Conditional Formatting that highlights cells when there was a change in another column Office 2013
Competent Performer
Conditional Formatting that highlights cells when there was a change in another column
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Thank you p45cal. The profile or census is done every end of quarter of the calenday year, so the profile date is March 31, 2019 while the comparison date is the previous end of quarter, that is Dec 31, 2018. I'm sorry for not providing the full details, otherwise so much guessing from good Samaritans like you would have been avoided. Taking cues from your formula, I came up with the following conditional formula, and it works fine.
=(VLOOKUP(DATEDIF($b14,$D12,"d"),$A$2:$D$10,4))<>$ D14
Any idea why range names do not work in conditional formatting?

Thanks also for the formula below, dispensing with the extra column computing the age in days and saving precious file space.

Quote:
=VLOOKUP(DATEDIF([@Birthday],ProfileDate,"d"),tAgeGroup,4)
Reply With Quote
  #4  
Old 04-28-2019, 03:48 AM
p45cal p45cal is offline Conditional Formatting that highlights cells when there was a change in another column Windows 10 Conditional Formatting that highlights cells when there was a change in another column Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

are you sure $D12 in the cf formula shouldn't be $D$12?
Reply With Quote
  #5  
Old 04-28-2019, 07:34 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formatting that highlights cells when there was a change in another column Windows 7 32bit Conditional Formatting that highlights cells when there was a change in another column Office 2013
Competent Performer
Conditional Formatting that highlights cells when there was a change in another column
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Right, it should be $D$12. Again, thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to highlight the cells using conditional formatting? LearnerExcel Excel 1 01-01-2019 01:43 AM
How to fill a cell at row and column intersection with conditional Formatting Iraj Excel 10 12-02-2017 09:54 AM
Conditional Formatting that highlights cells when there was a change in another column How to highlight cells using Vlookup / conditional formatting mikehk Excel 2 09-18-2017 07:30 PM
Conditional Formatting a column Washbue1 Excel 3 01-27-2017 01:11 PM
Conditional Formatting that highlights cells when there was a change in another column Conditional Formatting for Cells Containing Formula Gillian623 Excel 1 12-09-2016 11:03 PM


All times are GMT -7. The time now is 05:56 AM.


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