Surface Pro (2017) vs Surface Pro 4
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 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!!
Even tried! not tied!
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.
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).
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.
You need to use a CF formula like
You should be able to copy such a CF across and down.
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.
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.
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.