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.
The figure below shows the three columns involved.
Select the range B2:B9.
On the Home ribbon click the Conditional Formatting icon and select New Rule.
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
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.
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
See image below.
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.
Conditional formats are a great way to highlight either, what needs to be done, or as in our case, what has been done.