# Conditional formating based on values entered into other cells

#### warrima

##### New Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Yard

##### Well-known Member
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

##### MrExcel MVP
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

##### Banned
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

##### New Member
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

##### MrExcel MVP, Moderator
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
ABCDE
1Specified1111
21111
31111
41121
51121
61132
7
Cond Format
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B11. / Formula is =A1="Specified"Abc
D11. / Formula is =C1<>C2Abc

Last edited:

#### warrima

##### New Member
Thanks that works perfectly

Replies
14
Views
333
Replies
3
Views
273
Replies
4
Views
373
Replies
1
Views
134
Replies
9
Views
304

1,191,383
Messages
5,986,305
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back