Excel – Change Format When Updated

Here’s the problem. You have a list of parts and prices, you want to manually update each price and you want the format to change for each price that has been updated. How can you do it?

Well, before resorting to a macro you could copy the column of prices to another column and then use conditional formatting to compare the two columns. This will allow you to change the cell fill colour when the new price is different to the old price.

Demonstration

The figure below shows the three columns involved.

Parts_01

Select the range B2:B9.
On the Home ribbon click the Conditional Formatting icon and select New Rule.

Parts_02

There are a couple of ways to achieve this. The first is to use the second option in the Rule Type section and then select “not equal to” in the second drop down and type

=C2

in the third box – see image below. Make sure there are no $ signs in the cell reference. Use the Format button to select a Fill Colour. I used blue.

formatchange

Another way involves using a formula. Select the last option in the Rule Type section and then enter the following formula in the formula box

=B2<>C2

See image below.

Parts_03

It is important that there are no $ signs used in this formula. When you create a formula for a range you need to create the formula to work on the first cell in that range, B2 in our case. The <> in the formula means “not equal to”. The formula will return TRUE if B2 and C2 are not equal which will trigger the format change. I’ve use a blue cell fill colour format. You can click on the Format button to change the format.

The figure below shows two changes.

Parts_04

Conditional formats are a great way to highlight either, what needs to be done, or as in our case, what has been done.

Please note: I reserve the right to delete comments that are offensive or off-topic.