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