Change cell color if value is changed??

Eternal731

Board Regular
Joined
Jun 16, 2009
Messages
55
Hello all,

I have been looking around for awhile, and have not been able to find a way to do this.

Basically I am trying to figure out a way to change the color of a cell if the original value changes, as if to say this cell has been updated/changed.

Scenario, im creating 2010 budget form worksheets for Regional Managers, each have multiple stores. Budget items will be listed A:A (100 or so), months Jan-Dec in the column headers, one worksheet for each store.

My plan was to overlay the prior years actual expenses and income as a starting point or guideline, then when a cell was changed/updated it would change the color or the cell to indicate that, that particular item has been accounted for.

Any alternate ideas would be great or a solution to the color changing thing. I'd post an example except I don't know how to tag it in the post. Thanks in advance
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Well if you have your original budget as Sheet1 and your new budget as Sheet 2, then in Sheet2 you can apply conditional formatting to each cell.

Select all the cells in the shhet (with the cursor in A1) and then select Format/conditional formatting

Click on the left dropdown list and select 'Formula is'

In the field that opens type: =A1<>'Sheet1'!A1
Then from the formatting select say red background.
Click OK
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Right click the sheet tab and select view code and paste this in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 5
End Sub

Change 5 to represent the colorindex you want the cell to turn to. Now only a changed cell will change colour. ;)
 

Eternal731

Board Regular
Joined
Jun 16, 2009
Messages
55
Sheet1 is like a master list of budget items, thats to say that the prior years actual budget is not formatted exactly the same, its exported to excel from quickbooks, store by store, so the columns and rows will never match up perfectly.
 

Eternal731

Board Regular
Joined
Jun 16, 2009
Messages
55

ADVERTISEMENT

o_O that code does in fact change the color of the cell, but now that cell will not calculate in the formulas on the worksheet and there is no way to Undo the change. Effectively, undoing the cell coloring is changing the cell which is triggering the code, which changes the color of the cell, its a vicious cycle...lol.

No dice unfortunately, but thanks.
 

tanmay.bajpai

New Member
Joined
Sep 29, 2011
Messages
1
Hello Experts. Can someone kindly help me with a VBA code or conditional formatting which changes a cell color only when a pre-entered cell value is changed? If value is entered in a blank cell then color should not change.
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734

ADVERTISEMENT

Tanmay, please open a new thread for this question, else the chance of getting a response is small. Also it is not good habit to break into a thread with related but different question
 
Last edited:

caelewis

New Member
Joined
Aug 17, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Right click the sheet tab and select view code and paste this in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 5
End Sub

Change 5 to represent the colorindex you want the cell to turn to. Now only a changed cell will change colour. ;)

Hi Dave,
This VBA is super helpful! Thank you.
Is there a way to change the colour back if the original value is put back in the cell?
Thank you,
Craig
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top