Problem with code. Please help.

[pegbol]

Please, would anyone can help me with the next code?. It gives me an error.

Sub eeff()
Dim lastR As Long, ws3 As Worksheet
Set ws3 = Sheets("consolidado")
lastR = Range("b65536").End(xlUp).Row
Application.ScreenUpdating = False
With ws3
With Range("k8:k" & lastR)
'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" _ '<------ I THINK ERROR IS HERE
Or .Value >= 0.01 And .Offset(0, -1).Value = "H" Then 
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
End With
Application.ScreenUpdating = True
Set ws3 = Nothing
End Sub

Thanks in advance for your kind assistance.
.
.

[Jacob Hilderbrand]

With Range("k8:k" & lastR)

This is a range of cells. So you will have a problem when you try to use .Value.

Try something like this.

For i = 8 to lastR
With Range("K" & i)
'Your Code
End With
Next i

[CBrine]

Jake,
I don't think that the problem. LastR is a Long that represents a row number.

lastR = Range("b65536").End(xlUp).Row

A portion is trying to act on a multiple range object as though it's a single value. I think he needs to cycle through the range, something like this.

Sub eeff() 
Dim lastR As Long, ws3 As Worksheet, R as range 
Set ws3 = Sheets("consolidado") 
lastR = Range("b65536").End(xlUp).Row 
Application.ScreenUpdating = False 
With ws3 
With Range("k8:k" & lastR) 
'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 
 End With 
for each R in Range("k8:k" & lastR)
'COLOR RED COLUMN K
If r.Value < 0 And r.Offset(0, -1).Value = "D" _ '<------ I THINK ERROR IS HERE
Or r.Value >= 0.01 And r.Offset(0, -1).Value = "H" Then 
r.Font.ColorIndex = 2 
r.Interior.ColorIndex = 3 
End If 
Next
End With 
Application.ScreenUpdating = True 
Set ws3 = Nothing 
End Sub

HTH
Cal

[Jacob Hilderbrand]

A portion is trying to act on a multiple range object as though it's a single value. I think he needs to cycle through the range, something like this.

Isn't that what I said. Where he is trying to check the .Value of his With Range he needs to loop through them instead.

[CBrine]

Sorry Jake,
I didn't read it throughly.

[pegbol]

.
.
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

[Jacob Hilderbrand]

If you have 4 or less formats that you want to use based on certain conditions, have a look at Contitional Formatting (Format | Conditional Formatting).

• Select Column K

• Format | Conditional Formatting

• Formula Is

=And($K1=-10,$J1="D")

• Format as needed

• Add your other conditions and formats

[pegbol]

Thanks so much Jake!!!

Your advice right to the point.



regards,
Pedro.

[Jacob Hilderbrand]

You're Welcome

Take Care