Conditional Format a range - help needed.

[Chris Mitchell]

I want to apply Conditional Formatting to a range of cells such that if:

B3 >= A3, C3 is formatted one way (green), else another way (red),
B4 >= A4, C4 is formatted one way (green), else another way (red),
B5 >= A5, C5 is formatted one way (green), else another way (red),
Etc
Etc

I can do this line but line, but for many lines this is tedious.

Is there a formula I can apply to a range of cells that will do this.

I suspect that there may be a VB solution, but I am not at all familiar with
how to use VB, so if this is the way I would need comprehensive guidance,
starting with how to open/activate VB what to type where etc, i.e. I am a VB
virgin.

 

[Pete_UK]

One way if you have already set things up in cell C3 is the use the
Format Painter - select C3, click on the Format Painter icon, then
select the other cells in the range C4 downwards.

You can also do it in one operation (in future) by first selecting all
the cells that you want this to apply to, i.e. from C3 down, with C3
as the active cell. Then use the normal Format cells to set your
default condition to all of them (i.e. red), and then bring up the
Conditional Formatting dialogue box with the range still selected and
choose Formula Is rather than Cell Value Is, and enter this formula:

=B3>=A3

Click on the Format button and set the colour to green. Click OK twice
to exit the CF dialogue box. Excel will automatically adjust the
formula to the other cells in the range. CF takes precedent over
normal formatting, so you don't need two separate CF conditions.

Hope this helps.

Pete

 

[Matt Richardson]

I want to apply Conditional Formatting to a range of cells such that if:

B3 >= A3, C3 is formatted one way (green), else another way (red),
B4 >= A4, C4 is formatted one way (green), else another way (red),
B5 >= A5, C5 is formatted one way (green), else another way (red),
Etc
Etc

I can do this line but line, but for many lines this is tedious.

Is there a formula I can apply to a range of cells that will do this.

I suspect that there may be a VB solution, but I am not at all familiar with
how to use VB, so if this is the way I would need comprehensive guidance,
starting with how to open/activate VB what to type where etc, i.e. I am aVB
virgin.

Highlight all of the cells you want formatted in this way and choose
conditional formatting from the menu.

For your first condition, choose 'Formula is' from the dropdown and in
the box (assuming that the first cell of the highlighted range is C3),
type:-

=$B3>=$A3

And set the formatting to Green.

The formula will fill down the rest of the highlighted range rather
than you having to individually create conditions per cell.

HTH
Matt
http://2toria.com
http://teachr.blogspot.com

 

[Chris Mitchell]

Thanks Pete_UK.

I like the approach which hadn't occurred to me. Pre-formatting everything
red before applying CF also has the advantage of failing safe, i.e. would
make me look at it.


One way if you have already set things up in cell C3 is the use the
Format Painter - select C3, click on the Format Painter icon, then
select the other cells in the range C4 downwards.

You can also do it in one operation (in future) by first selecting all
the cells that you want this to apply to, i.e. from C3 down, with C3
as the active cell. Then use the normal Format cells to set your
default condition to all of them (i.e. red), and then bring up the
Conditional Formatting dialogue box with the range still selected and
choose Formula Is rather than Cell Value Is, and enter this formula:

=B3>=A3

Click on the Format button and set the colour to green. Click OK twice
to exit the CF dialogue box. Excel will automatically adjust the
formula to the other cells in the range. CF takes precedent over
normal formatting, so you don't need two separate CF conditions.

Hope this helps.

Pete

 

[Chris Mitchell]

Thanks Matt Richardson.

I've noted your suggestion for future use of CF but have gone with Pete_UK's
suggestion on this occasion.

I want to apply Conditional Formatting to a range of cells such that if:

B3 >= A3, C3 is formatted one way (green), else another way (red),
B4 >= A4, C4 is formatted one way (green), else another way (red),
B5 >= A5, C5 is formatted one way (green), else another way (red),
Etc
Etc

I can do this line but line, but for many lines this is tedious.

Is there a formula I can apply to a range of cells that will do this.

I suspect that there may be a VB solution, but I am not at all familiar
with
how to use VB, so if this is the way I would need comprehensive guidance,
starting with how to open/activate VB what to type where etc, i.e. I am a
VB
virgin.

Highlight all of the cells you want formatted in this way and choose
conditional formatting from the menu.

For your first condition, choose 'Formula is' from the dropdown and in
the box (assuming that the first cell of the highlighted range is C3),
type:-

=$B3>=$A3

And set the formatting to Green.

The formula will fill down the rest of the highlighted range rather
than you having to individually create conditions per cell.

HTH
Matt
http://2toria.com
http://teachr.blogspot.com

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete