Comparing Values and highlighting

[Navvy]

Hi all...

So far, I've have used VBA to manipulate a file exported into Excel. Also, I have taken out all rows with data where the date is greater than today's date (i.e. keeping today and past dates only) using the date column. Here is an example of the file:

NB INSTRUMENT DATE VALUE
13243 ULU/ADB 17/01/02 132.14000000000
21344 ULU/ADB 18/01/02 132.73000000000
12323 ULU/ADB 22/01/02 134.34000000000
24343 ULU/ADB 23/01/02 97.05355000000
43541 ULU/ADB 02/01/02 132.08000000000
23124 EIT/ULU 17/01/02 0.88120000000
34134 EIT/ULU 18/01/02 0.88020000000
34254 EIT/ULU 22/01/02 0.88280000000
45454 EIT/ULU 23/01/02 0.98736100000


The part I need help on is for the fourth column (Value). I want to highlight the value where the percentage change is greater than +/-10% from the previous one, ie: highlight (say in red), 97.05355000000 and 0.98736100000. It will need to reset each time the instrument changes, so in this case from ULU/ADB ---> EIT/ULU.

So, for example, we could:
0.98736100000 - 0.88280000000 = 0.104561

Now, 0.104561 / 0.88280000000 = 0.118442455

0.118442455 * 100 = 11.8% ---> HIGHLIGHT IN RED

If anyone has any solutions or better ways of going about this issue, please let me know.

A BIG thank you in advance!

 

[gcole]

Try an if statement and use the activecell properties to change the background color.

 

[SkipVought]

Hi,
You can use Conditional Formatting -- Format/Consitional Formatting ....

=ABS(D2-D3)/D2>.1

then do the formatting Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Hi Guys...

I'm pretty new to VBA so may need a kick-start here! If possible, please could you help me more.

Many many thanks in advance...Navvy

 

[SkipVought]

Conditional formatting is a Menu Item...

Format/Consitional Formatting

It is NOT VBA - it is part of the functionality of Excel.

But...
if you want a VBA solution

    Dim rngValues As Range, rngValue As Range
    Dim PrevVal
    'this defines the range of values
    Set rngValues = Range(Cells(2, 4), Cells(2, 4).End(xlDown))
    
    PrevVal = Empty
    For Each rngValue In rngValues
        With rngValue
            If Not IsEmpty(PrevVal) Then
                If PrevVal <> 0 Then
                    If Abs(.Value - PrevVal) / PrevVal > 0.1 Then
                        rngValue.Font.ColorIndex = 3
                    Else
                        rngValue.Font.ColorIndex = xlAutomatic
                    End If
                End If
            End If
            PrevVal = .Value
        End With
    Next

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Thank you, Skip. You are an asset to this Forum!

One more query, the code doesn't seem to account for when the NB and Instrument Column change, i.e:

NB INSTRUMENT DATE VALUE
13243 ULU/ADB 17/01/02 132.14000000000
13243 ULU/ADB 18/01/02 132.73000000000
13243 ULU/ADB 22/01/02 134.34000000000
13243 ULU/ADB 23/01/02 97.05355000000 (red)
23124 ULU/ADB 02/01/02 132.08000000000 (red)
23124 EIT/ULU 17/01/02 0.88120000000
23124 EIT/ULU 18/01/02 0.88020000000
23124 EIT/ULU 22/01/02 0.88280000000
23124 EIT/ULU 23/01/02 0.98736100000 (red)

So, the line:

23124 ULU/ADB 02/01/02 132.08000000000 (red)

...should not be in red as NB has changed from 13243 to 23124.

Any assistance would be (once again) appreciated!!!

A very humble...Navvy!!!

 

[Navvy]

Thank you, Skip. You are an asset to this Forum!

One more query, the code doesn't seem to account for when the NB and Instrument Column change, i.e:

NB INSTRUMENT DATE VALUE
13243 ULU/ADB 17/01/02 132.14000000000
13243 ULU/ADB 18/01/02 132.73000000000
13243 ULU/ADB 22/01/02 134.34000000000
13243 ULU/ADB 23/01/02 97.05355000000 (red)
23124 ULU/ADB 02/01/02 132.08000000000 (red)
23124 EIT/ULU 17/01/02 0.88120000000
23124 EIT/ULU 18/01/02 0.88020000000
23124 EIT/ULU 22/01/02 0.88280000000
23124 EIT/ULU 23/01/02 0.98736100000 (red)

So, the line:

23124 ULU/ADB 02/01/02 132.08000000000 (red)

...should not be in red as NB has changed from 13243 to 23124.

Any assistance would be (once again) appreciated!!!

A very humble...Navvy!!!

 

[SkipVought]

Well, your comparison would then include each value that needs to be tested, so you would state...

    Dim rngValues As Range, rngValue As Range
    Dim PrevVal, PrevNB, PrevInstr, iColNB, iColInstr
    
    For Each Heading In Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
       With Heading
          Select Case .Value
             Case "NB"
                iColNB = .Column
             Case "INSTRUMENT"
                iColInstr = .Column
          End Select
       End With
    Next
    
    'this defines the range of values
    Set rngValues = Range(Cells(2, 4), Cells(2, 4).End(xlDown))
    
    PrevVal = Empty
    For Each rngValue In rngValues
        With rngValue
            If (Cells(.Row, iColNB).Value = PrevNB) And _
                (Cells(.Row, iColInstr).Value = PrevInstr) Then
                    If Not IsEmpty(PrevVal) Then
                        If PrevVal <> 0 Then
                            If Abs(.Value - PrevVal) / PrevVal > 0.1 Then
                                rngValue.Font.ColorIndex = 3
                            Else
                                rngValue.Font.ColorIndex = xlAutomatic
                            End If
                        End If
                    End If
            End If
            PrevVal = .Value
            PrevNB = Cells(.Row, iColNB).Value
            PrevInstr = Cells(.Row, iColInstr).Value
        End With
    Next

Hope this helps ;-) Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Skip - thats fantastic! Thank you so much!

Have a good weekend.