Conditional Formatting formula using another cell not working

[MVCoder]

I have a cell, W6, with a formula to determine the number of days before/after today =IF(ISBLANK($L5),"",($V5-TODAY())*-1) and I wish to use a conditional format for another cell to indicate if this number is between -30 days and 0 (change the fill), or equal or greater than 0 (different fill).

When I use a conditional format formula AND($W6>-31,$W6<0) it works fine, but I can't find a way to get it to work for =>0.
I've tried formatting W6 as a Number, and as Custom -#,+#, but neither helped.
I've tried combinations like $W6>-1, or $W6=>0 without success.

All suggestions appreciated, thank you!

[etaf]

new rule
use
$W6>=0
format different fill colour
If the number is greater than 0 in W6
should work

See the yellow banner - follow instructions and load a sample spreadsheet

[MVCoder]

Thank you for the quick response but it didn't work, the cell (A6) I'm trying to conditionally format remains stubbornly without a fill even though the number in W6 is +2.

BTW, I have the formula in W5 and just enter a number (-3 or 3) in W6. A5 also conditionally formats the -30 to zero and fails to format the >0, just like A6, the only difference is the formula in W5 and the plain integer in W6. Hope that's clear, I think it demonstrates the formula in the W column doesn't effect the outcome.

Thanks again

[etaf]

i think a spreadsheet as mentioned - see full instruction in yellow banner

[MVCoder]

Oops, sorry 1st time...
Sample posted.

[etaf]

dont see anything in column W
perhaps a bit of an explanation where to look etc

[MVCoder]

Ok, my bad, rookie mess up, let me start over...
Please consider the new XLSample.xlxm I'm uploading.
Col A is a sequence number, B a user-entered date, C is col B +3 months, D a countdown/up of days up to and beyond today.
The real worksheet has too many columns to view on a monitor so col A is frozen. If there is a problem with another col, that column is conditionally formatted to have a different fill, in the case of col D in the sample, yellow within 30 days, red from 0 (today) and up.
I'm trying to conditionally format Col A with fill if any of the other unseen columns are also filled. This alerts the user to look for the problem.
As you can see in the sample, col D successfully fills depending upon the value, yellow for neg (row 3), red for 0 and plus (row 4).
Col A fills correctly if col D is negative as in row 3, but won't fill if col D is positive as in row 4.

I've tried to format Col D both as a number and as custom +#,-# without success, (see the format for D3 and D4). Also, tried various versions of the conditional format formula for => -1 or 0.

Suggestions or alternative strategies appreciated.

[etaf]

i dont think your applies to range is large enough
also the formula has " " around


this
AND($W6>-31,$W6<0
which is NOW
=AND($D4>-31,$D4<0)
should not be D4 unless that the only range you have applied

Select say A3: D1000, as row 3 is where the data row starts thats where the formula starts
so
=AND($D3>-31,$D3<0)
Will highlight the row from column A to D

Next
you have
="$d4>-1"
as the formula - and so that should be
also the formula has " " around it, so will be seen as TEXT and NOT a formula
change to
=$d4>-1
BUT as the selection is starts at 3 then so should the formula
=$d3>-1
Now because a blank cell will be seen as 0 and thats greater than -1
use
=AND($D3<>"", $D3>-1)

see attached - where i added that rule for red

otherwise clear all the rules from the conditional formatting and describe what colours you want for what formulas