Formating a sheet for hour input, with different text color depending on height of input

[Gonio]

Hi there,

First of sorry for my poor English. Am no native speaking and writing person. Thx in advance for any assistance. I am forced to make this in Excel 2007, so it has to work in that.

I have to adjust a sheet that is used to store the expected hours on a project. People fill in their hours on the sheet for a certain month, what we use to indicate the needed hours for that project. It is just typed in currently, but we might change the method of filling in to a VBA form later. For now if some one wants to adjust something they just type the new number.

What I want it to be able to do, is adjusting the value added depending on the type of input, and color the number accordingly depending on a few rules. But my understanding is not advanced enough to get this working. I have no objection for any method to get this working: a formula, VBA code, something else... Allthough it might be handy for future purpose if it is in VBA code that I can aply to a certain range of cells. Anyway thought I could maybe get it to work with a formula or maybe multiple formulas in combination with rules, but feel free for suggestions.

So these are kinda the rules I want to apply for all the cells in a certain range, that are intended to be filled.
1: If the cell is empty (blanc) I want the value being input shown in a blue color. This is basically to indicate for people viewing that the number is sort of newly added.
2: If there is already a value in the cell, but it is changed with a relative slightly small number (max 7 difference in values) compared to the previous input, it needs a yellow color. Example: current value is 5, but they change it to 9. Since there was already an existing value the color gets a yellow value.
3: Is related and similar to 2, but if it is increased with a big ammount that equals 8 or more (8 is an example in this case), I want the number to show up in orange. This is to indicate a unexpected big increase.

4: This is bit of an addition I would like to have, but no idea on how to make that work + I might go al wrong on how to achieve it. So suggestions are welcome. Once we know the hours are set in stone, we want the numbers to show up in green text color and overrule the previous colors from rules 1-3.

I personally thought of the following:
I like to have a small check box that can be put on the side of the data input and it must be able to always stay visible, even when scrolling. Unchecked rules 1-3 apply. Once checked every number they fill in is with green text color.

1-3 are really needed. The 4th is more optional. Thanks for any assistance and explanation.

Gonio

 

[cerfani]

Once you have a form to do this then you can have VBA code in the control events to do all this like clicking buttons... but for now you can use a worksheet change event.

This is a subroutine you will create that Excel will call any time the contents of a cell changes... the idea is to check the range being changed and you have the ability to read the value and make any formatting decision you like...

Worksheet Change Event: https://msdn.microsoft.com/en-us/library/office/ff839775.aspx

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If IsEmpty(Target) Then
            'color target range blue here
        End If 
    End If 
End Sub

just follow the instructions in the link to set it up... you put the code on the worksheet's code page (not a module)

 

[Gonio]

Thx Cerfani. I will give this a go and see if I can manage with the help from the link you added.