Formatting Cells using an If Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Seanb - 06 Sep 2007 15:50 GMT

Hi, I am wondering if anyone knows, is there a way to format a cell based on
it's value using an IF statement? For example I want to enter a worksheet
function that would change a cells font color to red if it exceeds a certain
value.

=if(A2>2, change fornt to red, do nothing)

I am not looking to do this in VBA do to the various range sizes and shapes
that i would have to reference (would be a nightmare), and I dont want to use
conditional formatting either. It would be fine and easy to use conditional
formatting, but the cells I am trying to evaluate are on different tabs (wont
let me link the formula to other tabs), and the structure of spreadhseet is
such that there are literally hundreds maybe thousands of cells that this
should be applied to. It will be no small task to try and use the
conditional formatting wozard, lets put it that way.

Ideally, I would just like to have a simple IF function that I can copy and
paste and find and replace get other people here to use also.

Is there anyway to do this via an IF and skip the conditional formatting
wizard? Any help is greatly appreciated!

BTW - I am using Excel 2000

Reply to this Message

Dave Peterson - 06 Sep 2007 16:10 GMT

You can give that cell on the other sheet a range name and then use that in your
format|Coditional formatting rules.

Select the cell on the other sheet (say Sheet2!A1)
insert name define
give it a nice name: MyLimit

Then select your other cells that get conditional formatting:
formula is:
=mylimit>2

> Hi, I am wondering if anyone knows, is there a way to format a cell based on
> it's value using an IF statement? For example I want to enter a worksheet
[quoted text clipped - 19 lines]
>
> BTW - I am using Excel 2000

Signature

Dave Peterson

Reply to this Message

Seanb - 06 Sep 2007 16:30 GMT

Thanks Dave, I thought of doing that to, and thats probably what I will have
to do as a very last resort, if there is no way to do it via an IF fn. but
it still does not get me around having to use the conditional formatting
wizard

> You can give that cell on the other sheet a range name and then use that in your
> format|Coditional formatting rules.
[quoted text clipped - 30 lines]
> >
> > BTW - I am using Excel 2000

Reply to this Message

Dave Peterson - 06 Sep 2007 16:34 GMT

Formulas in cells return values--not formatting.

And they can't change values or colors in other cells, either.

====
Another alternative if you don't want to name cells.

Formula is:
=INDIRECT("'sheet2'!a1")>2

This always points to A1 of sheet2--even if you insert/delete rows or columns.

> Thanks Dave, I thought of doing that to, and thats probably what I will have
> to do as a very last resort, if there is no way to do it via an IF fn. but
[quoted text clipped - 39 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 06 Sep 2007 16:39 GMT

ps. I see you've posted in another newsgroup.

I'll drop out of this thread.

> Formulas in cells return values--not formatting.
>
[quoted text clipped - 55 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: