Change cell color if value is changed??

[Eternal731]

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

 

[sijpie]

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]

Right click the sheet tab and select view code and paste this in

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]

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]

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]

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]

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

 

[caelewis]

Right click the sheet tab and select view code and paste this in

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

 

[Joe4]

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

Re-posted here: VBA - Cell Colour Change if value of cell changes

Please do not post your questions multiple times, as per rule 12 here: Message Board Rules

 

[TaitterRN]

Right click the sheet tab and select view code and paste this in

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.

I seriously just made an account to thank you for this.