VBA Conditional Format - Does Not Equal - Highlight Cell Yellow - Copy to Last Row

[MrSourApple]

I am trying to use vba to create conditional formatting rule for a range.

If cell J8 does not equal "N/A" I want the cell to be highlighted yellow. I then need to copy cell J8 and paste down to the last row of data in column J (rows will vary so I can't specify a row).

Right now I have the following code, but the issue I am having is that it highlights the cell regardless of the value. When I check the conditional formatting rules it shows the correct formula, but for format it says "No Format Set". So I think this code is just formatting all of the cells yellow and it isn't being considered a part of the "conditional formatting" rule.

With Range("J8")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
            Formula1:="=J8<>""N/A"""
     Interior.Color = vbYellow
End With

Dim CopyDown As Long
CopyDown = ActiveSheet.UsedRange.Rows.Count
Range("J8").AutoFill Destination:=Range("J8:J" & CopyDown)

 

[Jonmo1]

There's something missing here...

With Range("J8")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
            Formula1:="=J8<>""N/A"""
     [COLOR=#FF0000]Interior.Color = vbYellow[/COLOR]
End With

Try

With Range("J8")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
           Formula1:="=J8<>""N/A"""
    With .FormatConditions(1).Interior
        .Color = vbYellow
    End With
End With

 

[Jonmo1]

Also, on your autofill line, you may want to include the option of fillformatsonly.
Otherwise it will fill down the values as well as the formats..

Range("J8").AutoFill Destination:=Range("J8:J" & CopyDown), Type:=xlFillFormats

 

[MrSourApple]

Thank you for both comments. The formatting worked great. As for the autofill, I am not worried about it copying down because the cell being copied has a formula in it and will update automatically based on another cell.

Also, on your autofill line, you may want to include the option of fillformatsonly.
Otherwise it will fill down the values as well as the formats..

Range("J8").AutoFill Destination:=Range("J8:J" & CopyDown), Type:=xlFillFormats

One question though about the auto fill - is it possible to autofill multiple ranges without declaring a separate variable for each. For example the code below doesn't work but I think it explains what I mean:

Range("J8,P8,V8").AutoFill Destination:=Range("J8:J,P8:P,V8:V" & CopyDown)

 

[Jonmo1]

I was actually going to suggest doing it this way..

Instead of putting the format in 1 cell, Then filling down..

Put the format in the whole range all at once.

Dim CopyDown As Long
CopyDown = ActiveSheet.UsedRange.Rows.Count
With Range("J8:J" & CopyDown)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=J8<>""N/A"""
    With .FormatConditions(1).Interior
        .Color = vbYellow
    End With
End With