Solved

Excel Condition Formatting Help

Posted on 2013-11-12
11
248 Views
Last Modified: 2013-11-12
Hi,

A simple question hopefully.

I have two date columns with date which I want to conditionally format base on the following;

if Cell A3-A2<14 then format the background orange
if Cell A3-A2 is negative then format the background red

I am running Excel 2010.

Now I would like this to be relative to each row in in the column? (i.e. A3-A2. B3-B2. etc)
0
Comment
Question by:flynny
  • 5
  • 4
  • 2
11 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39641619
Select the cells to format, then go to Home|Conditional Formatting, then New Rule.

Select "use a formula to determine which cells to format"

Enter formula:

=A3-A2<0

click Format and choose Red from the Fill tab.

Click Ok,

Then click New Rule and repeat with formula:

=A3-A2<14 and select Orange.

click Ok.

Now make sure that the Red rule is at the top of the list of Rules.

Click Ok to finish.
0
 
LVL 12

Expert Comment

by:mattclarified
ID: 39641631
Hi,

Go into conditional formatting in the home tab, select new rule, and then select "Use a formula to determine which cells to format" then enter your formula as =A3-A2<14 for the first one and A3-A2<0 for the second one and then set the format options.
You can set multiple rules for the same cell and adjust the order for which you want them to apply in manage rules.

M@
0
 

Author Comment

by:flynny
ID: 39641679
Hi guys,

thanks for this.

This is the stage i am at but it appears to just format based on the first cell formula and not relative to the row its on.

I have the following formulae;

=$G$4-$F$4>0  which affect the range =$F$4:$F$186 and will set the backgorund red
=$G$4-$F$4<14 which affects the range =$F$4:$F$186 and sets the background orange

the red rule is at the top. However, all the cells are highlighting as orange backgrounds based on the first line (i.e. if i change F4 to be less than G4 then all the cells turn red).

Is there something I have missed?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39641699
Shouldn't the first formula be:

=$G$4-$F$4<0

because you said you want red if negative
0
 

Author Comment

by:flynny
ID: 39641718
sorry typo I was testing to see if red would override by setting it to somethign true.

it is in fact;

=$G$4-$F$4<0

however all the cells are appearing as orange based on the first cell formula rather than being relative
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:mattclarified
ID: 39641741
Hi,

Try putting rules at the top that say =ISBLANK(F4) and =ISBLANK(G4) and set the background format to Automatic

M@
0
 

Author Comment

by:flynny
ID: 39641777
No doesnt seem to fix anything.
spreadsheet1.png
spreadsheet2.png
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39641794
Try moving the "Stop if True" checkbox to the Red rule.

You don't need the Isblank rules.
0
 

Author Comment

by:flynny
ID: 39641822
Hi

I unchecked the stop if true and also tried moving it to the red box.

It seems to just be calculating based on the ther first values? as if I change the date in G4 to be 01/01/1999

the cells changes as per the attached image.
spreadsheet3.png
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39641899
In the conditional formulas, you should not have the $ before the 4's

try changing to $G4-$F4<0

and $G4-$F4<14

to not colour the blank rows, change formulas to:

=AND($G4-$F4<0,$G4<>"",$F4<>"")

=AND($G4-$F4<14,$G4<>"",$F4<>"")
0
 

Author Closing Comment

by:flynny
ID: 39641916
Brilliant, many thanks for the help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

22 Experts available now in Live!

Get 1:1 Help Now