Solved

Excel 2007 - Conditional Formatting of a Row based on a Date and a Text Field

Posted on 2013-11-03
2
271 Views
Last Modified: 2013-11-07
I have a list of invoices with the usual Invoice Number, Invoice Date, Client Name, etc. columns.

I want to change the colour to Red if the value in the Invoice Date column is over 45 days AND if the value in the Paid column is "No".

I was able to figure out how to apply the format change to the Invoice Date column if over 45 days but was unable to add the condition based on the Paid column. And extending it over the whole row? Couldn't figure that out yet...

Anyone able to help me out?

Thanks very much,

John
0
Comment
Question by:243johnm
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
Hello John,

Lets assume your data is in A2:E100 with Invoice date in column B and and "Paid" column is E

Select that whole range A2:E100 and apply the formula that applies for the first row of data in conditional formatting, i.e. using the "use a formula to determine which cells to format" option

=AND(TODAY()-$B2>45,$E2="No")

That will automatically format the whole row and apply to the range you selected

You need to use the $ signs exactly as I did, see example attached

regards, barry
cf-45days.xlsx
0
 
LVL 1

Author Closing Comment

by:243johnm
Comment Utility
Thanks barryhoudini, worked like a charm ... once I figured out how to apply it properly! Certainly isn't intuitive. And Excel formula language? Don't get me started, much prefer the formula language in Crystal Reports!

Thanks again,

John
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now