mardi 25 août 2015

vba code does not work on the first number in the loop but okay for the left numbers in the loop

I want to change the color of cells automatically if a cell is 10% bigger or smaller than either the corresponding cell that is in the same row but three columns to the left or the corresponding cell (in the same row and column) in the workbook '2014variance.xlsx'.

Sub testing1()

Dim x As Integer

Dim y As Integer

For x = 35 To 5 Step -3

For y = 11 To 76 Step 1

If IsNumeric(Cells(y, x).Value) Then

If VBA.Abs(Cells(y, x).Value) < VBA.Abs(0.9 * Cells(y, x - 3).Value) Or _

   VBA.Abs(Cells(y, x).Value) > VBA.Abs(1.1 * Cells(y, x - 3)) Then

     Cells(y, x).Interior.ColorIndex = 22

ElseIf VBA.Abs(Cells(y, x).Value) < VBA.Abs(0.9 *            
      Workbooks("2014variance.xlsx").Worksheets("Sheet1").Cells(y, x)) Or _

       VBA.Abs(Cells(y, x).Value) > VBA.Abs(1.1 * 
       Workbooks("2014variance.xlsx").Worksheets("Sheet1").Cells(y, x)) Then

     Cells(y, x).Interior.ColorIndex = 42


End If

End If

Next y

Next x

End Sub

The code works fine except for the Column 2. I understand that it will not be colored under the first if condition. However, I want it to work under the 'ElseIf' condition. Column 2 is supposed to have blue color because those numbers are lower than 90% of '2014variance.xlsx'. How can I do that? Thank you very much.

Aucun commentaire:

Enregistrer un commentaire