Conditional formatting, formula breakdown .

for RED use
=AND($D5<=TODAY()+35,$D5<>"")
for yellow use
=AND($D5<=TODAY()+50,$D5<>"")

Apply to the range
$A$5:$I$500
or what ever range you want to apply to

order the rules for red to be first and stop if true

see the attached

Happy to answer

If the formula was in a cell - you could have used an IF to put a result if true or false

in conditional formatting the format is only applied if the condition is true - so you only need the condition to test
Hence no IF

ie
in condition formatting
=B2=0
that would be true if cell B2 = 0 and then would apply the condition when true

1. when is "and" good to use for?.

AND
is a logical function when you want various conditions to be tested

B2=0 AND C2=0 AND D2=0 will be true when all those formula are TRUE - HENCE the AND

to write that out as a formula
=AND(B2=0 , C2=0 , D2=0)

There is also an OR function
B2=0 OR C2=0 OR D2=0 will be true when any of those formula are TRUE - HENCE the OR

2. "$D5 did you lock D5 because you are only worry about that date so that you can apply the conditional formatting just on that date and row. right?. I think so.

NO
because you wanted the ROW highlighted when D is TRUE
if i had put D5 and applied to A5:I200
the column would also move in the formula
so it would test A5,B5,C5
I only want when in CELL A5 to test if D5
so the $ locks that in and applies to the ROW

3.Obviously I'm reading the formula wrong, this is what I understand. when i translate your formula i read this. "add D5 plus today's date plus 35, but i don't what $D<>"") does.

one of the TESTs
=AND($D5<=TODAY()+35,$D5<>"")

today()+35
so add 35 to todays date

now i test to see if the date in D5 is less than or equal to today()+35days
today() = 5/6/14
today() + 35 = 10/7/14 (UK dates format - DD/MM/YY)

Is D5 date less that 10/7/14 - if that is TRUE - then 1 part of the AND is TRUE
but we have to have both parts TRUE

if D5 is blank - excel will see that as zero - and so 1/1/1900
which is less that 10/7/14
so all blank ROWS will also highlight
to stop that I want to test if the cell is blank
D5=""
BUT , i want it to only highlight when it is NOT blank
<> is not equal
2nd part of the formula
$D5<>""

if D5 is NOT a blank cell - will be TRUE

The AND will only return a TRUE if both those formula are TRUE
and highlight the row

hope that helps - if not let us know