Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 09-07-2017, 07:30 PM
mikehk mikehk is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
Novice
Need help with conditional formatting
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default Need help with conditional formatting

Hi,



I need help with conditional formatting, I have tried
Highlight rules but am unable to get it to work.

I am trying to get an indication on which payment to chase.
for example if the contract is for 1 year starting 15/May/2017 to 14/May/2018,
payments fall due on every month starting 15th.

Once the cells are highlighted in say green color, I would want the color to change
to yellow after 3 days indicating the payment is past due, then the color to change
to red after 6 days indicating the payment is over due.

I think I may have to add a column for "paid", once it's paid there is no
need to highlight cells.

Sorry, my English is not so good....so if there is anything missing/unexplained
please message to me.

I have attached herewith a sample test file, hope it helps.

Thank you.
Attached Files
File Type: xlsx mn_alerts.xlsx (9.6 KB, 17 views)
  #2  
Old 09-11-2017, 05:10 AM
NBVC's Avatar
NBVC NBVC is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Select from A4 to D7 and try these conditional formatting formulas:

=AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>=6) for orange

=AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>=3) for blue

=AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>0) for green

in that order
  #3  
Old 09-17-2017, 08:33 PM
mikehk mikehk is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
Novice
Need help with conditional formatting
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Thanks a lot.
I will try it out and revert back if any issues.

Best regards
  #4  
Old 09-22-2017, 05:30 AM
NBVC's Avatar
NBVC NBVC is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Please also let us know if it works.
  #5  
Old 09-22-2017, 06:11 PM
mikehk mikehk is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
Novice
Need help with conditional formatting
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Sure, will do.

Thanks.
  #6  
Old 09-22-2017, 06:21 PM
mikehk mikehk is offline Need help with conditional formatting Windows 10 Need help with conditional formatting Office 2013
Novice
Need help with conditional formatting
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Actually, I am working on an another project and I am stuck at copying the formula, may be you can help me with that.

I have a total of 13 sheets ...12 of them renamed to Jan, Feb, Mar, Apr...upto Dec,
Sheet13 renamed to Accounts.

In each sheet Jan to Dec ...on daily basis data is entered. Everything is fine. In the
Account sheet data is taken from each sheet (Jan to Dec)...

for example (in Account sheet)
=IF(Jan!$A1="","",(Jan!$A$2))
=IF(Jan!$A3="","",(Jan!$A$4))
=IF(Jan!$A5="","",(Jan!$A$6))
Its easy to copy paste the formula for all 31 days in Jan, but when it comes
to Feb, how do I copy paste the formula? for example

=IF(Feb!$A1="","",(Feb!$A$2))
=IF(Feb!$A1="","",(Feb!$A$2))
=IF(Feb!$A3="","",(Feb!$A$4))

rename Jan to Feb, then Jan to Mar, Jan to Apr...and so on...

Please help.

Thanks.
  #7  
Old 09-22-2017, 11:20 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need help with conditional formatting Windows 7 64bit Need help with conditional formatting 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

Your last post is a duplicate post for https://www.msofficeforums.com/excel...t1-sheet2.html
Please do not ask the same question in different threads.
You can always "bump" a thread if you do not receive helpful answers.
This thread is now closed
__________________
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
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with conditional formatting Conditional formatting bbutl027 Excel 15 12-05-2016 08:56 PM
Conditional Formatting sdfrance Excel 3 01-06-2015 07:10 AM
Need help with conditional formatting Conditional formatting that ignores other formatting rules info_guy2 Excel 1 07-03-2014 10:07 AM
Conditional Formatting jdc15371 Excel 2 06-26-2014 06:34 AM
Conditional Formatting teza2k06 Excel 3 01-19-2014 02:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:51 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