Cell Formatting of a Conditional Statement

R

RobFJ

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
 
J

Jacob Skaria

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.
 
M

Ms-Exl-Learner

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<>"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top