Conditional Formatting

[Nick Wakeham]

Am using Excel 2000 and want to have Conditional Formatting in cells.

I want cells that are greater than a number to turn green.

The cell I am Conditional Formatting also contains an 'IF' formula and turns
green whether there is any number in it or not. Is there any way I can get
it to Conditional Format only when a number appears in the cell?

Hope I have made myself clear

Nikwak

 

[Dave Peterson]

I used A1 for my cell.

Format|conditional formatting:
formula is:
=AND(ISNUMBER(A1),A1>5)

Change 5 to what you want.

 

[SteveG]

Nick,

This should do it for you.

Conditionl format in the "Formula Is" option


=AND(A1>?,A1<>"")

This is where A1 is where your IF statment is and also your where the
conditional format is. ? is the number that you want A1 to be greater
than to turn the cell green.

Cheers,

Steve

 

[Nick Wakeham]

Thanks guys - that's been a huge help

Nikwak

 

[Nick Wakeham]

Oops! Still a problem:

I don't think I explained it really clearly - got myself confused, so will
break it all down in the hope it makes more sense:

The spreadsheet has a column whereby there are about 120 entries. All these
entries have to go either Green, Red or Yellow if they hit a certain target
(eg red if they are less than 5, yellow if between 5 and 10 and green if
over 10)

However, the formula in these cells, to find out the value, is something
like =IF(H17<>0,H17/G17,"")

The red and the yellow conditional formatting seems to work - as does the
green but if, as in above example, H17<>0 then the workout cell turns GREEN,
when it should in point of fact be RED as it is less than 5.

Hope that explains things a bit clearer and that someone will instantly
recognise what is going wrong

Nikwak

 

[Stephen]

Oops! Still a problem:

I don't think I explained it really clearly - got myself confused, so will
break it all down in the hope it makes more sense:

The spreadsheet has a column whereby there are about 120 entries. All
these entries have to go either Green, Red or Yellow if they hit a certain
target (eg red if they are less than 5, yellow if between 5 and 10 and
green if over 10)

However, the formula in these cells, to find out the value, is something
like =IF(H17<>0,H17/G17,"")

The red and the yellow conditional formatting seems to work - as does the
green but if, as in above example, H17<>0 then the workout cell turns
GREEN, when it should in point of fact be RED as it is less than 5.

Hope that explains things a bit clearer and that someone will instantly
recognise what is going wrong

Nikwak

H17<>0 is not a valid test. You can have H17<0 or you can have H17>0 but not
both together. If you really need both conditions you must have two separate
tests combined using the OR function. However, unless you have negative
numbers, H17>0 alone may be quite adequate. Depending on the circumstances,
you may need to use instead H17>0.001 or something similar, to avoid an
extremely small number (resulting from a rounding error) triggering the
condition.

 

[JE McGimpsey]

H17<>0 is a valid test for "H17 not equal to zero".

 

[Nick Wakeham]

Stephen

Thanks for that.

The formula to get rid of '#VALUE!' etc is ok, that seems to work but I take
on board what you have pointed out. It is the Conditional Formatting that
seems to be picking up the fact that there is a number in the cell that is
equal to or greater than 10.

For instance, I have in cell J19 the formula =IF(H19>0,G19/H19,"") BUT I
have Conditionally Formatted cell J19 to turn it red if value is under '9',
yellow if between '9 and 10' and green if '11' or over. I think it is
picking up the number '19' and therefore turning the cell green. What I need
to do is, if there is no actual value showing in the cell (ie '0' or under)
that it either remains clear or turns red.

Hope that explains it clearer

Nikwak

 

[SteveG]

Ok. In your first post, it appeared that if the cell was blank, you did
not want to have any fill for the cell. The formula you just used as an
example returns a blank if H17 does not = zero so therefore, it is not
less than 5, there is no value in the cell per say when it comes to the
conditional formatting unless you omit the AND F17 <> "". With the last
proposed solution I sent, it is telling it to color only if the cell is
less than 5 and also not blank. I just used these three conditions and
it changed correctly for me sticking to your first post where a blank
cell should not be filled. F17 is where I entered the conditional
format and used your IF statement that refers to H17 and G17.

1.
Formula is:

=AND(F17<5,F17<>"") ---Format Red

2.
Formula is:

=AND(F17>=5,F17<>"",F17<=10) ---Format Yellow

3.
Formula is:

=F17>10 ---Format Green


Does that help?

Steve

 

[Stephen]

Sorry, I misunderstood and I'm still not clear about what you mean by "it is
picking up the number 19".

Anyway, my suggestion is that you apply the conditional formatting in the
order green, yellow, red. That is (and thinking only of integers for
simplicity), first test for H19>10 (green).

The second test is for H19>8 (yellow); note that it doesn't get to this
test if it has gone green, so H19>8 means that it =9 or 10.

The third test is for H19>0 (red); by similar logic this means 1 to 8
inclusive. Anything else (0 or negative) will not trigger the conditional
formatting, so will leave the cell clear.

 

[SteveG]

I think I got lost somewhere. Are your conditons being based off the
result of your formula in cell J19 or are they being based off the
values in H19 or G19? If it is the resulting value of the formula in
cell J19, the condition will always turn green in Stephen's last post
if the formula returns a blank. That is because the condition views
the formula in the cell as a value greater than 0, 8 or 10. You need
to accomodate for the result of that using the AND function. To your
Green and Yellow conditions add.


=AND(J19>10,J19<>"") ----Green

=AND(J19>8,J19<>"") ----Yellow

Continuing with Stephen's post for the Red condition, that will turn
J19 Red if the value in J19 equals "". If you want it to be clear,
apply the same method as above to the Red condition.

=AND(J19>0,J19<>"") ---Red



HTH

Steve

 

[Nick Wakeham]

Stephen

Thanks for this but I am now as confused as I have made everyone else -
sorry!

What I meant by "it is picking up the number 19"was that I thought the
conditional formatting was picking up the fact that there was the number 19
in cell J19 and therefore the conditional formatting was seeing the number
19 and thinking it was over 10 so showed Green.

I am confused as to what the formula should now be in cell J19, which, at
the moment has =IF(H19>0,G19/H19,"") with the conditional formatting side of
it done through Format/Conditional Formatting on the toolbar.

Nick