Put a conditional format into a LastRow code

[WasWodge]

Hi there, if anyone can help.
Is there a way I can put a simple conditional format statement into a piece of LastRow code like

Sub addFormula3()
    Dim LastRow As Long
    With Sheet1    
        LastRow = .UsedRange.Rows.Count
        .Range(.Cells(2, 16), .Cells(LastRow, 16)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""OVER CAPACITY"","""")"
   
    End With
End Sub

conditional format will be "IF N2 > P2 THEN = COLOUR YELLOW AND BOLD"

Or any other way of achieving the same?

[Leith Ross]

Hello Paul,

This is written for Excel 2003. Not sure if this will work the same on later versions of Excel.

Sub addFormula3()

  Dim Cell As Range
  Dim FC As Object
  Dim Rng As Range
  
    Set Rng = Sheet1.Range("P2")
    Set Rng = Rng.Resize(Sheet1.UsedRange.Rows.Count - Rng.Row + 1, 1)
    
    Rng.FormulaR1C1 = "=IF(RC[-2]>RC[-1],""OVER CAPACITY"","""")"
    
     For Each Cell In Rng
       If Cell.FormatConditions.Count > 0 Then
          Cell.FormatConditions.Delete
       End If
       Set FC = Cell.FormatConditions.Add(xlExpression, , "=IF(RC[-2]>RC[-1],True,False)")
       FC.Font.Bold = True
       FC.Font.ColorIndex = 6
     Next Cell
     
End Sub

[WasWodge]

Leith, Thanks for your help so far but I haven't got it to work in 2007 yet but I will have a play around later as it is giving an object required error at

Set Rng = Sheet1.Range("P2")

[WasWodge]

Update.
Leith, I had been trying to put it in a std module. Put it in a sheet module and is working correctly.
Thanks again

[Leith Ross]

Hello Paul,

Glad you got it working. I assumed with 117 posts to your credit that including instructions on installing the macro wasn't needed. That's the trouble with assumptions.