Formula to highlight a number change "from" - "to"

[juliew1961]

I am working on condition assessments which are conducted on a yearly basis. 1-5 with 5 meaning "needs replacement".

So I am looking for a formula so that when I enter a 5 over a 4 (say) it will highlight the cell with a style that will designate "was 4" or 5 over a 3 (say) it will highlight the cell with a style that will designate "was 3" etc etc.

Is this possible?

Would most appreciate some help - simple probably but I am a novice.

J

 

[JustynaMK]

Hi J,

The task wasn't that simple, but it is indeed possible

Let's assume that you have your 1-5 values stored in cells A1:A7, Sheet1. You need to open your VBA window and paste the following code into Sheet1 Worksheet (not "ThisWorkbook"):

Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = ActiveCell.Offset(-1, 0)


    If Not Application.Intersect(Range([B][COLOR=#ff8c00]"A1:A7"[/COLOR][/B]), Range(Target.Address)) Is Nothing Then
        Select Case rng.Value
        Case 5
            Select Case oldValue
                Case 1
                    rng.Interior.Color = [COLOR=#ff8c00][B]RGB(127, 187, 199)[/B][/COLOR]
                Case 2
                    rng.Interior.Color = [COLOR=#ff8c00][B]RGB(100, 135, 203)[/B][/COLOR]
                Case 3
                    rng.Interior.Color = [COLOR=#ff8c00][B]RGB(180, 53, 7)[/B][/COLOR]
                Case 4
                    rng.Interior.Color = [COLOR=#ff8c00][B]RGB(12, 250, 18)[/B][/COLOR]
            End Select
        Case Else
            Exit Sub
        End Select
    End If
End Sub

I have highlighted the most significant fields: you can change your range from A1:A7 to a range where your 1-5 values are being stored, you can also modify RGB colors for each case.

I hope it helps. Good luck.