Conditional formating based on values entered into other cells

[warrima]

1) What formula do i need to use in conditional formating to turn cell B1 black if cell A1 says "Specified".

2) I have a row of numbers in collumn C.

eg

1. 1111
2. 1111
3. 1111
4. 1121
5. 1121
6. 1132

In collumn D i need the cell to turn black when the number changes. So in my case cells C3, C5 and C6 should turn black. Is there a formula for conditional formating to allow this.

Many thanks

Thanks in advance

 

[Yard]

1) set your conditional formatting in Cell B1 so the formula is

=A1="Specified"

2) conditional formatting can only test the current values of cells, not when a cell is actually changed. I don't understand your example, since you refer to column D, then specify cells in column C. The principle is the same as in question 1), so setting the conditional formatting in D1 to:

=C1<>1111

will highlight D1 if C1 is not equal to 1111.

 

[Andrew Poulsom]

For your first question, Condition 1 Formula Is:

=A1="Specified"

For your second question why does C5 count as a change when it is the same as C4? If you want to compare with C1, for D1 Condition 1 Formula is:

=C1<>C$1

copied down.

 

[Stormseed]

Hi

First Question:

Select the cell B1, Open Conditional Formatting window, in Condition1 select "Formula Is" from drop down and enter this formula:

=IF(A1="specified",TRUE)

Click on Format, Patterns tab, select balck color and click on OK. You are ready.

I do not quite understand your second question.

 

[warrima]

Thanks for the posts guys. Just to clarify my second question!

Column C is a list of order numbers and collumn D is the order date. Basically i want the conditional formating to incourage the oprerator to enter a date in collumn D for each different order by turning the cell black.

 

[Peter_SSs]

warrima

Just clarifying the first issue. There is no need for an IF statement in your conditional format. The direct statement as given by Yard will do. If it is True, the format will be applied, if false the format will not be applied.

For the second one, see if this helps. I know I haven't used black but you can easily change the colour.

Excel Workbook
	A	B	C	D	E
1	Specified		1111
2			1111
3			1111
4			1121
5			1121
6			1132
7
Cond Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =A1="Specified"	Abc
D1	1. / Formula is =C1<>C2	Abc

 

[warrima]

Thanks that works perfectly