Conditional Formatting VBA Code

[howard]

I have data in columns A:J from row 12 onwards. I have written code to format the data in Col A:J if the value in col J is >=30 and three rows after the row in col J containing a value >=30.

When running the macro, it comes up wirth run time error 5 "Invalid procedure cal or argument and the following code is highlighted

.FormatConditions.Add Type:=xlExpression, Formula1:="=$J12>=30)"

Sub conditional_formating()
Finalrow = Range("a12").End(xlDown).Row
With Range("A12:J" & Finalrow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$J12>=30)"
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Resize(3).Interior.ColorIndex = 15
End With
End Sub


It would be appreciated if someone could assist

 

[VoG]

Try

.FormatConditions.Add Type:=xlExpression, Formula1:="=$J12>=30"

 

[howard]

Hi Peter

Thanks for the help. did not see )

After correcting this error, I run the macro again and the following code is also not correct

.FormatConditions(1).Resize(3).Interior.ColorIndex = 15

If col J >= 30, I want to extend the color that was formatted by another 3 rows

Your assistance in this regard is most appreciated

 

[VoG]

Maybe like this

.Resize(3).FormatConditions(1).Interior.ColorIndex = 15

 

[howard]

Hi peter

Thanks for the help, much appreciated