Excel/excel formula for conditional formatting
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.
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
if the range starts at A1, if it starts at A2 then you will use
The second part of the question. To highlight cells 2 months ahead of today's date, the formula would be.
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
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
The 1st condition is a formula condition done like the first answer
set the format to No Fill
View the image for a better understanding