.
.
That was it !!!!!. Now the code is working!!!!!
Thanks so much a lot DRJ.
A last favor, (I am a newbie in VBA). Is there any way to improve my code (I would like, it would be much faster).
regards,
Pedro
La Paz, BOLIVIA.
P.S.
What I am pretending to do with the code is:
After make the calculations with formula in columns K and L. Based on the value of each cell in columns K or L (wheter negative or positive). And based on the value of each cell in column J (wheter D or H). The respective cell in columns K or L would be red or not.
Example:
If K8 is: -10 , and J8 is: D. Result K8: color red.
If K8 is: 10 , and J8 is: H. Result K8: color red.
If K8 is: 10 , and J8 is: D. Result K8: no color.
If K8 is: -10 , and J8 is: H. Result K8: no color.
Sub eeff()
Dim lastR As Long, ws3 As Worksheet, i As Long
Set ws3 = Sheets("consolidado")
lastR = Range("b65536").End(xlUp).Row
Application.ScreenUpdating = False
With ws3
For i = 8 To lastR
With Range("k" & i)
'FORMULA COLUMN K
.FormulaR1C1 = _
"=IF(OR(RC[-2]=""ACT"",RC[-2]=""EGR""),IF(RC[-7]>RC[-6],RC[-7]-RC[-6],RC[-7]-RC[-6]),IF(RC[-6]>RC[-7],RC[-7]-RC[-6],RC[-7]-RC[-6]))"
.FormulaR1C1 = .Value
'COLOR RED COLUMN K
If .Value < 0 And .Offset(0, -1).Value = "D" _
Or .Value >= 0.01 And .Offset(0, -1).Value = "H" Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
With Range("l" & i)
'FORMULA COLUMN L
.FormulaR1C1 = _
"=IF(OR(RC[-3]=""ACT"",RC[-3]=""EGR""),IF(RC[-6]>RC[-5],RC[-6]-RC[-5],RC[-6]-RC[-5]),IF(RC[-5]>RC[-6],RC[-6]-RC[-5],RC[-6]-RC[-5]))"
.FormulaR1C1 = .Value
'COLOR RED COLUMN L
If .Value < 0 And .Offset(0, -2).Value = "D" _
Or .Value >= 0.01 And .Offset(0, -2).Value = "H" Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
Next i
'SUBTOTALS
With Range("k" & lastR + 1)
.FormulaR1C1 = "=subtotal(9,r8c:r[-1]c)"
.AutoFill Destination:=Range("k" & lastR + 1 & ":l" & lastR + 1)
End With
With Range("k" & lastR + 1 & ":l" & lastR + 1)
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlThin
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
Range("m1").Select
End With
Application.ScreenUpdating = True
Set ws3 = Nothing
End Sub