Highlight cell when formula result changes

[harbour1302]

Hi Folks,

I have an ordering sheet for my business.

In cells F4:I4 i input my forecast sales.

In cells G8:I19 the result tells me how much stock to order.

I Place all orders on a Monday but have aproblem that if during the week i need to adjust my forecast, this changes the amount i need to order.

If possible i require a VBA that highlights any cell that result has changed and remove the highlight when sheet closes.

Or a VBA code that automatically creates a duplicate sheet with the actual numerical change in the cell, foe example if i require 2 boxes less then the duplicate sheet will show -2.

Thanks

 

[harbour1302]

I have tried using the track changes tool but it does not track formula result changes.

 

[harbour1302]

Hey Guys,

This one has been allusive I am hoping one of the geniuses here can help.

I have an order calculator I developed for my business, I place all my orders Monday for the week, I am having an issue where if I should change my forecasted ales during the week the value of the amount ordered changes.

I would like a vba code to notice when a value has changed since last save and copy the entire sheet to a new workbook with the difference in values.

For example, if I originally needed to order 5 apples and the sales change I now need to order 7 apples; I would like a sheet to open with 2, in the apples row to show the difference I need to order. or -2 if I now require only 3 apples etc.

Is this even possible?

or does anyone have an alternative that would work, I have tried track changes but it doesn't work as all cells contain a formula.

Thanks so much!

 

[JoeMo]

Hey Guys,

This one has been allusive I am hoping one of the geniuses here can help.

I have an order calculator I developed for my business, I place all my orders Monday for the week, I am having an issue where if I should change my forecasted ales during the week the value of the amount ordered changes.

I would like a vba code to notice when a value has changed since last save and copy the entire sheet to a new workbook with the difference in values.

For example, if I originally needed to order 5 apples and the sales change I now need to order 7 apples; I would like a sheet to open with 2, in the apples row to show the difference I need to order. or -2 if I now require only 3 apples etc.

Is this even possible?

or does anyone have an alternative that would work, I have tried track changes but it doesn't work as all cells contain a formula.

Thanks so much!

You might improve your chances of getting some help on this by posting your worksheet layout and formulas using one of the tools from the links below. One deterrent to answering a post like this is that you provide very little detail and no information that can easily be copied from a browser and pasted directly to Excel to use for testing a proposed solution.

Excel Jeanie link: Download
MrExcel HTML Maker link: http://www.mrexcel.com/forum/2545970-post2.html

 

[harbour1302]

Hi,

I have included a portion of the spreadsheet i am using.

Excel Workbook
	A	B	C	E	F	G	H	I
1	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*
3	*	Delivery Period	*	*	Mon, Tues & Wed	Thurs & Fri	Sat, Sun & Mon	Tues & Wed
4	*	Sales Forecast (K)	*	*	8.2	6	10.5	3
5	*	*	*	*	*	*	*	*
6	Code	Product Description	Price	On Hand	Transfer In	Wednesday	Friday	Monday
7	Chilled and Produce:
8	27874	Cheese	30.05	4.1	*	1.0	3.0	1.0
9	60124	Hotdog	78.24	1.0	*	*	1.0	*
10	11001	Kepak Meat	42.60	18.9	*	4.0	16.0	5.0
11	60122	Bacon	40.04	3.3	*	*	2.0	1.0
12	75541	Capsicum Green 70/90Mm	8.99	1.1	*	*	*	*
13	75085	Iceberg Lettuce Vacuum Cooled.	5.40	3.5	*	*	3.0	*
14	75354	Chilli Jalepeno Green.	10.50	0.4	0.0	1.0	*	1.0
15	75140	Lemon Medium	1.45	0.5	*	*	*	*
16	27865	Mushrooms	13.10	1.1	0.1	1.0	2.0	*
17	75697	Onion Large	13.10	2.4	*	*	1.0	1.0
18	50994	Five Guys Potatoes	9.90	23.1	*	4.0	19.0	6.0
19	16073	Tomato Large Round (G) *1-6Kg	6.45	4.2	*	1.0	3.0	1.0
Order Calculator

Is this correct?

Thanks

 

[harbour1302]

So in F4:I4 I add forecast sales this adjusts the order amount based on a usage calculated from another sheet.

I require a way to highlight changes if i adjust the forecast sales after orders are placed to make it easier to amend them.

either highlight the changed values and show old value when hover or duplicate sheet with changes only.

Any help would be awesome.

Thanks

 

[JoeMo]

So in F4:I4 I add forecast sales this adjusts the order amount based on a usage calculated from another sheet.

I require a way to highlight changes if i adjust the forecast sales after orders are placed to make it easier to amend them.

either highlight the changed values and show old value when hover or duplicate sheet with changes only.

Any help would be awesome.

Thanks

You might consider a worksheet_change event module in the Calculator sheet that monitors F4:I4. Any change(s) to F4:I4 would trigger the event code which would need to include a way to determine if the change(s) is part of the initial sales forecast or a change to the initial forecast. If the former, the values in A3:I19 could be copied to a new sheet (the 'comparison' sheet) to set the "baseline" for the week. If the latter, a copy of the newly calculated A3:I19 values could be compared to the baseline and only the differences in F4:I4 and G8:I19 shown in a copy of the range A3:I19 pasted adjacent to the baseline values on the comparison sheet.

 

[harbour1302]

Hi Joe,

I have looked on google and your reply.

I agree the best course would be to have a worksheet_change event that would copy all old values to a 'hiddensheet' whenever a change occurs in F4:I4.

I could then have a third sheet called 'amended orders' that would have a basic formula in each cell that is the value from the 'hiddensheet' (old value) - the new value form the 'order calculator'.

could anyone help with a code for this?

I have found this but can't seem to get it to work.

Private Sub Workbook_Open()

Dim hiddenSheet As Worksheet

Set hiddenSheet = Me.Worksheets.AddhiddenSheet.Visible = xlSheetVeryHidden
hiddenSheet.Name = "HiddenSheet"

Sheet2.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Sheet2.UsedRange.Address)

End Sub

 

[JoeMo]

Hi Joe,

I have looked on google and your reply.

I agree the best course would be to have a worksheet_change event that would copy all old values to a 'hiddensheet' whenever a change occurs in F4:I4.

I could then have a third sheet called 'amended orders' that would have a basic formula in each cell that is the value from the 'hiddensheet' (old value) - the new value form the 'order calculator'.

could anyone help with a code for this?

I have found this but can't seem to get it to work.

Private Sub Workbook_Open()

Dim hiddenSheet As Worksheet

Set hiddenSheet = Me.Worksheets.AddhiddenSheet.Visible = xlSheetVeryHidden
hiddenSheet.Name = "HiddenSheet"

Sheet2.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Sheet2.UsedRange.Address)

End Sub

Someone here might be able to help you with this. It will require more time than I can afford to invest.

 

[harbour1302]

This is the code i have managed to source;

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As Variant, NewVal As Variant
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("MyRefRange")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
If IsNumeric(OldVal) And IsNumeric(NewVal) Then
' Copy Previous Values from F4:I4 to Sheet5
Sheet5.Cells(Target.Row, Target.Column).Value = OldVal
' Copy Previous Values from E8:I130 to Sheet5
Sheet2.Range("E8:I130").Copy
Sheet5.Range("E8").PasteSpecial (xlPasteValues)
Application.Goto Sheet5.Range("A1")
Application.CutCopyMode = xlCut
End If
Target.Value = NewVal
Application.Goto Sheet2.Cells(Target.Row, Target.Column)
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


However it copies all data when changes are made in F4:I4

This can make the old data inaccurate as it recopies all cells rather than only the cells that have had a change in value.

Can anyone assist i need a way to have all cells monitored for change (including cells that change as a result of formula) and update the old values if they change.