conditional format 3.1

hi experts, ive have added 2 conditional formats to my worksheet but doesnt work, please see the attached screenshot

conditional format 3.1screenshot_conditional-format-1.PNG
Frank .SBuilding EstimatorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frank .SBuilding EstimatorAuthor Commented:
- the only cell/s that should show in red are e3 & e7
- the only cell/s that should show in yellow is e5
0
RaminTechnical AdvisorCommented:
what is the condition to show  e3 & e7 in red ?
or let me ask this, why  e3 & e7  must be shown in red ?
0
Frank .SBuilding EstimatorAuthor Commented:
condition to show red fill color is greater than or = to f1 as it shows in the conditional format rules manager window
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RaminTechnical AdvisorCommented:
Because yellow condition is true and is the first condition then all cells all yellow.
0
Frank .SBuilding EstimatorAuthor Commented:
ok, so what needs to be done then?
0
Frank .SBuilding EstimatorAuthor Commented:
ok thanks
0
Ejgil HedegaardCommented:
With a formula condition you must include the first cell in the range in the formula
=E3=$F$1-1
0
RaminTechnical AdvisorCommented:
Ok, have a look at this:

1.png
0
RaminTechnical AdvisorCommented:
Use parentheses for your conditions:

2.jpg
0
Frank .SBuilding EstimatorAuthor Commented:
hi experts, im still unclear as to how to fix my formula, i can see on the screenshot above from ramin that it works but how do i relate that back to my data?
0
RaminTechnical AdvisorCommented:
change
$F$1-1
to
($F$1)-1

  aaa.jpg
0
RaminTechnical AdvisorCommented:
select first condition then select  Edit rule ...
then change that condition.

Feel free to ask any question.
0
Frank .SBuilding EstimatorAuthor Commented:
hi expert, i have used your conditional format formula but still does not work, see screenshot below

conditional format 1.1
0
Frank .SBuilding EstimatorAuthor Commented:
- cell e5 should be hilited yellow and its not
0
RaminTechnical AdvisorCommented:
You put that formula in a quotation. compare it with mine.
0
Frank .SBuilding EstimatorAuthor Commented:
i didnt put it in a quotation, it automatically changed to a quotation, i deleted the quotation marks and still no change
0
RaminTechnical AdvisorCommented:
Probably you do it in a different way,   try this way:

Select E3:E7 with mouse
Home
Conditional formatting
Manage Rules ...
Select the yellow condition
select Edit rules ...

Please check attached file.

Test.xlsx
0
Frank .SBuilding EstimatorAuthor Commented:
hi ramin, im still having problems with the conditional formatting, please see screenshot below.
The problem is cells e4 & e5 should be hilited in yellow because it meets condition 1, but its not hilited at all?
Please assist.

screenshot 1.2
0
RaminTechnical AdvisorCommented:
($D$1)-1 must be ($F$1)-1

AND also

$D$1 must be $F$1


D1 cell refers to a blank cell and has no value.


3.png
0
Frank .SBuilding EstimatorAuthor Commented:
hi ramin, please see the screenshot below i have changed the formula below as per your directions above but it keeps changing back to include the inverted commas before & after, i dont know what to do?

screenshot 1.3
0
RaminTechnical AdvisorCommented:
Please describe the steps you taken.
0
Frank .SBuilding EstimatorAuthor Commented:
ok,
1. hilited range of cells e3:e7
2. selected conditional formatting/ manage rules/ clicked on condition 1/ edit rule/ under "format only cells with" hilited & pasted your suggestion above (see below) over the top/ okay/ okay

($D$1)-1 must be ($F$1)-1

AND also

$D$1 must be $F$1
0
RaminTechnical AdvisorCommented:
You must select "Format only cells that contain"

xxxxxx.png
0
RaminTechnical AdvisorCommented:
Is it possible to attach your exce file ?
0
Frank .SBuilding EstimatorAuthor Commented:
sure, i will now for you
1
Frank .SBuilding EstimatorAuthor Commented:
attached is the excel file for you
protect---ifstatements_3.xlsx
0
RaminTechnical AdvisorCommented:
0
Rob HensonFinance AnalystCommented:
Your value for the comparison is actually in D1 although it looks like it is in F1 because of the merged cells.

The comparison value also includes a time value and not just a date whereas the values in column E are just date.

use the following for the Conditional formatting rules:

Yellow:   Cell Value = INT($D$1)-1
Red:        Cell Value >=INT($D$1)

This method will only apply to column E, if you want it applied to other columns as well you have to use the "Use a Formula to determine which cells to format" option. The formula in these case would be:

Yellow:          =$E3=INT($D$1)-1  Applies to =$A$3:$C$7,$E$3:$E$7,$G$3:$G$7
Red:              =$E3>=INT($D$1)    Applies to =$A$3:$C$7,$E$3:$E$7,$G$3:$G$7

See attached.

Thanks
protect---ifstatements_3--2-.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.