Using formulae to change the colour of cells in Excel

[joeafro84]

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.

 

[JE McGimpsey]

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.

 

[joeafro84]

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

 

[David McRitchie]

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

 

[joeafro84]

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.

 

[JE McGimpsey]

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

 

[joeafro84]

So it does! Sorry, I didn't quite understand it. The next question is
then, can I do this more than once for a selected range, for example
so that those values greater than are one colour, and those values
less than are another?

 

[joeafro84]

So it does! Sorry, I didn't quite understand it. The next question is
then, can I do this more than once for a selected range, for example
so that those values greater than are one colour, and those values
less than are another?

(Not to worry, I forgot about the add button, thanks for your help.)