Hi!
My approach is always to record actions, and tweak them afterwards. I've been using to much time in figuring out how to program this and that in Excel, to later find out it could easily be recorded.
OK - if I understand you correct, you wan't conditional formatting in your last column. The last column might be column c, d ...n ie dynamical based on the imported dataset.
Now, first I would remove all previously used conditional formatting. The Excel Macro Recorder provides the following code:
' select all cells with conditional formatting (available thru F5 in Exel)
activecell.specialcells(xlcelltypeallformatconditions).select
' delete current conditional formatting
selection.formatconditions.delete
After that, I'd probably place my cursor at the top left corner of the sheet, make Excel move the cursor to the last cell in the row (= last column), select entire column contents and apply conditional formatting.
Excel Macro Recorder provides this (with just minor tweaking):
Range("A1"

.Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="15"
With Selection.FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Font.ColorIndex = 41 ' Text color blue
.Interior.ColorIndex = 6 ' Back color (pattern) yellow
End With
The conditional formatting "selecting" can easily be modified to i e cell references
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$C$35", Formula2:="=$B$35"
btw - the lastrow thingie in VBAjedi's post, (should) get the row number of the last row providing lastrow is declared as a long (I think) - haven't used his notation, so I'm not entirely sure how it works.
HTH Roy-Vidar