I have a list of numbers, which is updated twice a week, and I want to
make it easy to track increases and decreases in these numbers. I
have read several of the posts on this forum, and many of them speak
of conditional formatting. I have tried using this, but it only
allows you to format one cell at a time, and as I have something like
85 numbers to check twice a week, this could be very time consuming.
Does anyone know of a formula I can fill across that would be
something like:

IF(A1<B1, B1's background goes red)

which I could then fill right to make:

IF(B1<C1, C1's background goes red)

and that could be filled down like so:

IF(A2<B2, B2's background goes red)

I would appreciate any help, thanks.

One way:

You can Conditionally format many cells at once.

Select, say, B1:J10, with B1 the active cell.

Choose Format/Conditional formatting and enter the following format:

CF1: Formula is =A1<B1
Format1: <pattern/<red

XL will adjust the formula for the other columns/rows.

In article . com,
wrote:

I have a list of numbers, which is updated twice a week, and I want to
make it easy to track increases and decreases in these numbers. I
have read several of the posts on this forum, and many of them speak
of conditional formatting. I have tried using this, but it only
allows you to format one cell at a time, and as I have something like
85 numbers to check twice a week, this could be very time consuming.
Does anyone know of a formula I can fill across that would be
something like:

IF(A1<B1, B1's background goes red)

which I could then fill right to make:

IF(B1<C1, C1's background goes red)

and that could be filled down like so:

IF(A2<B2, B2's background goes red)

I would appreciate any help, thanks.

It says "you cannot use a direct reference to a worksheet range in a
conditional formula".

I think you are getting ahead of yourself with that but if that actually
is your problem:

You may not use references to other worksheets or Workbooks
for Conditional Formatting criteria. This is not much of an obstacle,
simply use a named range to refer to a range on another worksheet.
The restriction also means that you cannot use a formula referencing
your personal.xls in a user defined function. You can get around that
by creating a reference in your VBE from your workbook to
your personal.xls

You did not indicate anything that was not on the worksheet in your
question, and John answered accordingly.

The cells that are selected when you enter the Conditional
Formatting are the cells that are subject to conditional
formatting. You could make your range B:J in the
example that John gave then you would not have to update
but keep in mind that your formula is relative to the active
cell in the range.

see http://www.mvps.org/dmcritchie/excel/condfmt.htm

if still not clear you might look at Mike Alexander's site
for a video http://www.datapigtechnologies.com/ExcelMain.htm
--

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message ups.com...
It says "you cannot use a direct reference to a worksheet range in a
conditional formula".

My problem is that I want to make every cell relate to the one to the
left of it and only that cell, so that the conditional formatting that
happens in C2 depends only on the data in B2. Then I need to copy the
same criteria down the whole of the C column, so that C3 only relates
to B3, C4 only to B4 etc. I have tried using a formula =LEN(C2) but
the results this gives bear no relation to my table.

I'm confused. The CF solution I gave you does exactly what you're asking
for.

In article .com,
wrote:

My problem is that I want to make every cell relate to the one to the
left of it and only that cell, so that the conditional formatting that
happens in C2 depends only on the data in B2. Then I need to copy the
same criteria down the whole of the C column, so that C3 only relates
to B3, C4 only to B4 etc. I have tried using a formula =LEN(C2) but
the results this gives bear no relation to my table.