flynny
asked on
Excel Condition Formatting Help
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)
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)
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@
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@
ASKER
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?
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?
Shouldn't the first formula be:
=$G$4-$F$4<0
because you said you want red if negative
=$G$4-$F$4<0
because you said you want red if negative
ASKER
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
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
Hi,
Try putting rules at the top that say =ISBLANK(F4) and =ISBLANK(G4) and set the background format to Automatic
M@
Try putting rules at the top that say =ISBLANK(F4) and =ISBLANK(G4) and set the background format to Automatic
M@
ASKER
Try moving the "Stop if True" checkbox to the Red rule.
You don't need the Isblank rules.
You don't need the Isblank rules.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant, many thanks for the help.
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.