You are here:

Excel/excel formula for conditional formatting

Advertisement


Question
QUESTION: i am making a simple stores inventory system in excel using office 2010 and want to do following

- i have a column of numbers representing remaining items in stock lets call it column A.
- also have a column of numbers representing minimum quantities for each item in column A lets call this column B
- I want the number in column A to highlight red when it goes below the number in column B

on another column i have validity dates, using office 2010 I have managed to conditional format them to appear red if date is last month, this month or next month. how do I also format to turn a different color in two months time.

regards

Peter

ANSWER: Hi Peter,

The first part of your question, the answer is also Conditional formatting.

Highlight the range in Column A. The conditional format formula will be =a1<b1 if the range starts at A1, if it starts at A2 then you will use =a2<b2.

The second part of the question. To highlight cells 2 months ahead of today's date, the formula would be.
=AND( TODAY()+60<A1,A1<>"") this assumes you range starts from A1.

So Today's date plus 60 days is less than the cell value.

1-Highlight the range for conditional formatting.
2-Select New rule=>use formula to determine which cells to format
3-In the format values where the formula is true is where the formula goes.
4-Select the type of format you want.

---------- FOLLOW-UP ----------

QUESTION: Dave Hi

thanks first part worked great

second part is not working because I phrased it incorrectly

what i really want is below

1) Cells highlighting for dates passed and due in 30 days
2) Cells highlighting for 30-60 days

Answer
Conditional formatting
Conditional formatting  
Sure thing,
Same way as before but instead
select Format only cells that contain as the rule type.
The first box select cell value , the next box select Less Than and the third box enter this =now()-30 for greater that 30 days and a second condition for greater than 60 days
=now()-60

The 1st condition is a formula condition done like the first answer
=ISBLANK(A1)=TRUE
set the format to No Fill

View the image for a better understanding
Dave
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


Dave Morrison

Expertise

I can answer most excel questions My Website is davesexcel.com

Experience

I have been working with spreadsheets since 1991. I am a self-trained excel developer since 2005.

Publications
My website davesexcel.com

Education/Credentials
Business admin. U of R.

Awards and Honors
Microsoft Excel MVP 2011-2012

©2016 About.com. All rights reserved.