Add color on input and check value difference between old and new input for cell (VBA)?

[Gonio]

Hey there,

Sorry for my poor English in advance, am no native speaking and writing person. This question has to work in Excel 2007.

For an sheet what we use to store the hours as an indication for what we need, I need a formula. But no idea how to get it done since my knowledge of Excel is moderate.
Multiple people fill in their hours and sometimes adjust them. So if it is previously 6 and they need more (9 for example), they just type in that specific cell 9 and thereby overwriting the 6.
Problem is that we want to track the changes with a formula and if needed add a cell color, but I am uncertain on what function accomplish this. I know how to format it, so I can add a cell color. It is the part from the change between old and new value where I am not certain on what to do.

So as example of what we want: If in cell C6 is a value of 6 and someone adjusts it to 14, the difference is 8. So the formula must detect that the change is equal or more then 8. With formatting it we make the rule that the cell is shown in orange. If the change between the old and new value is 7 or less, nothing happpens besides the value change.

Thx for the help in advance

Gonio

The above I posted a while back. Seems this can't be done in a formula. I know a bit of VBA, but doing what we want in VBA is not my strongsuit. If anyone can assist to acomplish above story in VBA I would be greatfull.
The color part is the most needed for now, but the check would also be handy for a future purpose with a userform to combine it with graphs for analysing purpose. This is assuming that it can be done VBA. Not sure if a check can be made comparing the old value of a cell and the new intended value trough a form?

1: If new input and there was no previous value the color would be blue
2: If value is already exisiting a small increase up to about 7 nothing happens, but as soon as it is equal or greater then 8 it needs a orange color

Thx for any feedback on this matter.

 

[JLGWhiz]

It would have to be done with change event code to capture the old value, do the math and then reinstate the new number. Here is the code. Assuming that cells C6 will be the entry cell. If this assumption is wrong, then please specify exactly which cell(s) are subject to change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim newNum As Long
Application.EnableEvents = False
    If Not Intersect(Target, Range("C6")) Is Nothing Then
        If IsNumeric(Target) And Target.Value > 0 Then
            newNum = Target.Value
            Application.Undo
            If Target.Value = "" Then
                Target = newNum
                Target.Interior.Color = vbBlue
                Application.EnableEvents = True
                Exit Sub
            ElseIf newNum - Target.Value >= 8 Then
                Target = newNum
                Target.Interior.ColorIndex = 45
            End If
        End If
    End If
Application.EnableEvents = True
End Sub

To use this code, right click the name tab of the worksheet where your data will be entered by the user, in the pop up menu, click 'View Code'. The vb editor will open to the worksheet code module where you can then paste the above code. Your workbook should be saved as a macro enabled workbook (.xlsm) with security settings to allow access to VBA and run macros. those are one time settings. If all preceding is done, then close the vb editor any when a change is made to cell C6 the macro will execute. Try different variations of entries to make sure it does what you want. If it does not do what you want, post back with specific information on what it is or is not doing, including any error messages you might get. Before you ask, no, I cannot change the shade of blue and orange. Well, I can, but I won't.

 

[Gonio]

Thx @JLhWhiz for this code. I havn't had the opportunity to use it yet, but seems you have pointed me in the right direction. Best method for me to learn is to toy around myself once I have a point of reference. And otherwise I asume by mentioning or quoting you, you get the notification.

 

[JLGWhiz]

Thx @JLhWhiz for this code. I havn't had the opportunity to use it yet, but seems you have pointed me in the right direction. Best method for me to learn is to toy around myself once I have a point of reference. And otherwise I asume by mentioning or quoting you, you get the notification.

Happy to assist. The way the notification works on this site is by subscription. When a user posts on a thread, they are automatically subscibed to that thread until they manually disconnect from it. So until they disconnect, they will receive an email for any new posts after their last post. Users can manabe their subscriptions by clicking on their user name at the top of the screen, which will open their activity page. You can look that over for yourself and figure out how to use it. It won't let you hurt anything, so feel free to click a few buttons to see what they do. That is how I learned to use it.
Regards, JLG

 

[Gonio]

Allright, had some time to look at it. I think I understand some of the code you wrote. But I tried to use it and everytime I do that I get the error Object needed. I guess that this means the code can't find the current selection on a sheet or something like that? Even with your code I am still uncertain how I make the code check the cells from a certain range. Must I put it in a form orso?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim newNum As Long
Application.EnableEvents = False
    If Not Intersect(Target, Range("C6")) Is Nothing Then
        If IsNumeric(Target) And Target.Value > 0 Then
            newNum = Target.Value
            Application.Undo

I have a bit trouble understanding your first part of the code so correct me if I am off/wrong.Added all I don't completely follow above:

1. I guess I must change the Worksheet_Change to the name of the sheet I want to use for my workbook. But what is the function of that ByVal?
2. Part with the Dim code: is that used to define that the values are numbers?
3. The Part with the Events Code: Is that needed for the check between current input and intended input?
4. What does the rest do?

Thanks in advance.