Thanks Max, works a treat.

Much appreciated!

AT

"Max" wrote:

Would this be in the conditional formatting criteria
or is it to replace the formula that I posted up originally?

The earlier suggestion's for use in the conditional formatting,
via the "Formula Is" option

With your further clarification, try it along these lines

In your sheet 2,

Assume you have the CF "limits" entered
in A1: 90
in A2: 60
in A3: 1

Then assume you want to conditionally format col C
Select col C (with C1 active)
Apply conditional formatting using the "Formula Is" option

Condition 1, Formula Is:
=AND(ISNUMBER(C1),C1=$A$3,C1<$A$2)
Format: Red fill

Condition 2, Formula Is:
=AND(ISNUMBER(C1),C1=$A$2,C1<$A$1)
Format: Yellow fill

Condition 3, Formula Is:
=AND(ISNUMBER(C1),C1=$A$1)
Format: Green fill

Click to OK out

The above is a more robust way to apply the CF, and should clear up your issue
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AT" wrote:
Thanks for the quick replies. Regarding the first reply from Max, I am unsure
where I would apply this. Would this be in the conditional formatting
criteria or is it to replace the formula that I posted up originally?

Anyway, I feel that I may have misrepresented my problem. The link is
between 2 sheets, so sheet 1 is where the formula is applied,
calculating/retrieving data from Sheet 2.

And finally, the critera for the conditional formatting to occur are linked
to cells which (as an example) contain the numbers 90 (Cell A1), 60 (Cell A2)
and 1 (Cell A3) (so that it's easy to adjust the variables). From here, I
setup the conditional formatting rules to state

Turn Green if Cell value is same or greater than Cell A1
Turn Yellow if Cell value Between A1 and A2
Turn Red if Cell value is Between A2 and A3

When I use the formula I mentioned in the first post, if there are numbers
in the cells it is pointing to (on a different sheet), after the simple
calculation, the cell will chage colour, corresponding to the value i.e. 49,
so the cell turns Red. However, when the cell that it is pointing to has no
value/entry, then it automatically changes Green rather than remain
blank/colourless.

I hope this makes it slightly clearer. Thanks for both individuals who have
made posts, I really appreciate it.


"David Biddulph" wrote:

You're missing a quote mark (or you've got a spare one). And unless you've
got a sheet called sheet2 and another sheet called sheet 2, you need to be
consistent with the spaces.
--
David Biddulph

"AT" wrote in message
...
.... I have used
the following to suppress the error msg so nothing comes up, but it fails
to
correct it.

=IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4)

So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process
F3/F4. To me, it looks like a simple process, but Excel doesnt like it.

If someone could shine some light on this, that would be much appreciated.