I want to be able to change the background of a cell colour if a second
condition is met - but for the background to remain unchanged if the first
condition is met.

So in the formula below, the background would be yellow if the 'c2'
condition is satisfied.

=if(c1>0,c1,c2)

My actual formulae are much more complex but if someone can point me in the
right direction in terms of principles that would be great

1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>

Based on your condition either go by 'Cell Value Is' or use
formula...Condition1>Select 'Formula Is' and enter the below formula

=A1=5
(If the cell value = 5)

OR (c2 is the condition)
=A1=c2


4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"RobFJ" wrote:

> I want to be able to change the background of a cell colour if a second
> condition is met - but for the background to remain unchanged if the first
> condition is met.
>
> So in the formula below, the background would be yellow if the 'c2'
> condition is satisfied.
>
> =if(c1>0,c1,c2)
>
> My actual formulae are much more complex but if someone can point me in the
> right direction in terms of principles that would be great
>

Thanks Jacob - the condition suggestion works just as I want it <BG>

Many thanks

"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:7AE9FB60-9891-4CFC-B9E6-(E-Mail Removed)...
> 1. Select the cell/Range (say A1:A10). Please note that the cell reference
> A1
> mentioned in the formula is the active cell in the selection. Active cell
> will have a white background even after selection
> 2. From menu Format>Conditional Formatting>
>
> Based on your condition either go by 'Cell Value Is' or use
> formula...Condition1>Select 'Formula Is' and enter the below formula
>
> =A1=5
> (If the cell value = 5)
>
> OR (c2 is the condition)
> =A1=c2
>
>
> 4. Click Format Button>Pattern and select your color (say Red)
> 5. Hit OK
>
> PS: If you are using XL2007 Goto Home tab>Styles>Conditional
> Formatting>Manage rules>New rule>Use a formula to determine which cells to
> format. Enter the formula in the box below.
>
> --
> Jacob (MVP - Excel)
>
>
> "RobFJ" wrote:
>
>> I want to be able to change the background of a cell colour if a second
>> condition is met - but for the background to remain unchanged if the
>> first
>> condition is met.
>>
>> So in the formula below, the background would be yellow if the 'c2'
>> condition is satisfied.
>>
>> =if(c1>0,c1,c2)
>>
>> My actual formulae are much more complex but if someone can point me in
>> the
>> right direction in terms of principles that would be great
>>
>

Not clear whether this is what you are looking for…

Place the cursor in C1 cell and select
Format>>Conditional Formatting>>Condition 1>>Formula Is>>
=$C1>0
Then Click the Format command button and choose your desired Font and
Pattern colour and give Ok Twice.

Now place the cursor in C2 cell and select
Format>>Conditional Formatting>>Condition 1>>Formula Is>>
=$C1<=0
Then Click the Format command button and choose your desired Font and
Pattern colour and give Ok Twice.

Assume that you would like to apply the conditional formatting Upto C500
cells. Then place the cursor in C1 cell and extend the selection upto C2 cell
by pressing Shift+Down arrow and press Cntrl+C to copy the selection (C1:C2).

Now place the cursor in C3 cell and extend the selection upto C500 and do
right click and select Paste Special (Note: Paste special will be active once
the copy has done, otherwise you can’t able to select the Paste Special
Option) and select the Formats Option Button and press Ok. Now the
Conditional formatting is applied upto C500 cells.

The C2 cell formula will highlight the C2 cell when C1 is <=0.
=$C1<=0
The above C2 cell formula will highlight the C2 cell when there is no data
in C2.

So, If you want to set colour for C2 when C1 is <=0 and C2 is not blank,
then use the below formula while applying the conditional formatting for C2
cell.
=AND($C1<=0,$C2<>"")

--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"RobFJ" wrote:

> I want to be able to change the background of a cell colour if a second
> condition is met - but for the background to remain unchanged if the first
> condition is met.
>
> So in the formula below, the background would be yellow if the 'c2'
> condition is satisfied.
>
> =if(c1>0,c1,c2)
>
> My actual formulae are much more complex but if someone can point me in the
> right direction in terms of principles that would be great
>