VBA control Condtonal formatting

[Gvlewis81]

Hi i've adapted the code below for a range of cells im tring to format.

Basically if the cell in column J's row is filled in fro A?:J? are to be filled.

I'm getting an error '1004' Method'Range' of object'_Global' Failed?

Any ideas ?

myrange = Range("A3:J" & Range("A2").End(xlDown).Row)
    
    
    With Range(myrange).FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=IF($J3<>"",TRUE,FALSE)")
        
    
        .Interior.Color = RGB(153, 204, 0)
       
    
    End With

 

[Gary McMaster]

With Range(myrange)should be either:


With myrange

or

With Range("A3:J" & Range("A2").End(xlDown).Row)

 

[Gvlewis81]

I still get the error on this part;

With myrange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=IF($J3<>"",TRUE,FALSE)")

 

[Gary McMaster]

Guess I'm not following your request. You shouldn't need VBA for conditional formatting.

Are you trying to use the "Worksheet Change" event to color A thru J when J changes?

 

[Gvlewis81]

Hi,

I'm creating a new sheet in the workbook and want to add the conditional formatting with VBA.

 

[Gary McMaster]

I'm creating a new sheet in the workbook and want to add the conditional formatting with VBA.

Maybe this:

Public Sub Test()

Dim oFormat As Range

Set oFormat = ActiveSheet.Range("A3:J" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)

oFormat.FormatConditions.Add Type:=xlExpression, Formula1:="=$J3<>"""""
oFormat.FormatConditions(1).Interior.Color = RGB(153, 204, 0)

End Sub

 

[Gvlewis81]

Think that may have done it thank you