Conditional Formatting Excel 2007

  TonyV 19:53 14 Oct 2010

I want to format a goodly number of cells so that if the condition is met it will show Red figures or Green figures. I can do this row by row, but is there a quick way of doing a number of rows in one go? i.e. The governing cells are B5 to B21, and I want to conditionally format the subsequent cells with the new format using B5 to B22 as the base that governs the condition. In other words if cell C5 is equal to B5, the response should be in Green, if it is less than B5, it should be in Red.



  VoG II 20:20 14 Oct 2010

Select the whole range then enter the CF formula to refer to the top left cell in the range. Excel will adjust the CF formula in subsequent rows.

  TonyV 20:42 14 Oct 2010

Thanks for that. I have tied to do what you say, but when I select cells C5 to say AI22, The top LH Cell is now C5, and I then make the CF to cover that, but it will only allow me to pick Cell B5 which is the first of the governing cells. I need all the B cells 5 thro' 22 to be the governing cells for their own rows.

Is there some way that I can Select the total area I want, then select the top LH Cell of the range to apply the CF? Or are you saying that the total range includes the B range as well? I tried that and ended up with a lemon!!



  TonyV 20:43 14 Oct 2010

Even tried! not tied!


  KremmenUK 07:08 15 Oct 2010

I'm not sure I've seen a formula that allows font or background colour changing. Vog may well know of one.

I think I would be creating a Macro to do this.

Your going to need to 'Cells' 'Select' the top left one and work across then down via the 'Offset' and/or 'Cells' or 'Range' keywords to change the colours as required as you go.

A good starting point would be to 'record' a Macro and then fiddle with it to get it to do what you want.

  VoG II 08:39 15 Oct 2010

You may need to anchor the formula using the $ as in this example click here

The simplest way is to select the whole range and apply the CF, as stated above. An alternative is to get it working on one cell then click in that cell, click the Format Painter (on the Home tab) and drag to the other cells.

Using CF you can change the colours, borders etc. but you cannot change the font or font size (the dialog will appear but those options are greyed out).

  TonyV 10:11 15 Oct 2010

I have tried the Format Painter, but of course, the governing Cell is shown as anchored ($), so, by definition, it can't be copied across a complete range only a row change in this instance!

It seems that when applying CF, the governing cell is automatically anchored! I need a number of governing cells to be applied.

KremmenUK, Thanks, but unfortunately, I don't do macros!! There is a way of formatting what I want, all I want is to do a large area rather than by row at a time.



  VoG II 10:53 15 Oct 2010

You need to use a CF formula like


You should be able to copy such a CF across and down.

  TonyV 13:54 15 Oct 2010

Thanks for your efforts. Though I have to say I am no further forward trying to complete a block transfer of Format. If I have B5 as 4, and B6 as 6 and B7 as 3 et al, down to B22 which happens to be 3, If I use B5 as the base number, but try to get it to relate to B6 down to B22,everything is related to 4, which is the number not anchored in B5, but is anchored in the CF formula.

To get over the problem, I have applied CF to cells C5 to C22 and then Format Painted as far along the individual rows as I want, at the moment as far as Column AZ.

Either I am missing something or am making a bitches breakfast of applying the formula.

However, thanks for your patience.


  TonyV 14:06 15 Oct 2010

As a matter of interest, when I try to do a multiple collection formula in the CF element, the message comes up that :- "You cannot use a direct reference to a worksheet range in a CF formula. Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)".

So on the basis of that comment, I'm not sure how we can relate to a series of cells, in my case, B5:B22, which govern the end result of the Conditional Formatting.



  VoG II 14:13 15 Oct 2010

If you select the range and use the CF formula


doesn't that work?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Comment afficher des fichiers cachés sur Mac ?