Conditional Formatting Excel 2007

  TonyV 19:53 14 Oct 10
Locked

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.

Thanks

TonyV

  VoG II 20:20 14 Oct 10

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 10

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!!

Thanks

TonyV

  TonyV 20:43 14 Oct 10

Even tried! not tied!

TonyV

  KremmenUK 07:08 15 Oct 10

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 10

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 10

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.

Cheers

TonyV

  VoG II 10:53 15 Oct 10

You need to use a CF formula like

=$B1=1

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

  TonyV 13:54 15 Oct 10

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

  TonyV 14:06 15 Oct 10

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.

Cheers

TonyV

  VoG II 14:13 15 Oct 10

If you select the range and use the CF formula

=$C5=$B5

doesn't that work?

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…