You are here:

Excel/Conditional Formatting

Advertisement


Question
Hello Jerry,

I currently have a Invoice summary register in excel where the details of the invoice are automatically updated in the registered using a macro on the invoice template.  However in the summary register there is a column called Accounts to call where the person who has created the invoice manually types in a date when the accounts lady has to follow this invoice up.  I have put in a conditional format formula so that on the day that the call needs to be made the cell comes up pink.  The formula is as follows:
=IF(AND(N17<=TODAY(),N17>0),TRUE,FALSE).  My problem is this:  When a invoice is created and macro then the information is always transferred to line 15 on the summary register and then it creates a new line above it, but the conditional formatting formula moves down with the rest of the cells so that when the person types the date into the accounts call date column in row 16 there is no conditional formatting formula unless this is manually updated each time.  Is there a way to fix the cell number in the conditional formatting formula so that it stays at N15?   

I hope this makes sense and I look forward to hearing from you.  Really appreciate any help you can give me on this.

Thanks a lot

Kind regards

Marcie

Answer
First, you can simplify your CF formula.  By definition your CF formula is a TRUE/FALSE construct, so you don't need to test the results and put in your own TRUE/FALSE.

For instance, in a cell, put in this de facto FALSE formula:

=10=8

Since 10 doesn't equal 8, you get a FALSE answer.  I didn't need to test the formula and insert my own FALSE, it happens by design.

So your CF formula would simply be:  

=AND(N17<=TODAY(), N17>0)


Now to your question.  I would COPY an existing row in its entirety, like row 16, then insert that copy at row15, then I would clear the cells with any constants leaving only formulas behind, if there are any.  This way your formatting including conditional formatting is copied as well.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.