Conditional Formating in excel 2007 using macro (VBA)

[madmaxxx89]

Hello

I found that thread and tried to use the code for my Problem.
http://forums.techguy.org/business-a...ormatting.html

I adjusted the Range and played a litte bit around with the code but I can't get it work.
what I got until now is this:
Sub Conditional_Formating()
'
Dim Lrow As Long

Lrow = Cells(Rows.Count, 1).End(xlUp).Row

With Range("4:4", Selection.End(xlDown))

.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(VALUE($C4)"",$C4:C" & Lrow & ")"
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
.FormatConditions(1).Interior.TintAndShade = 0.799981688894314
.FormatConditions(1).PatternTintAndShade = 0

End With

End Sub

But I dont know how to use the Formula: The macro should apply the conditional formating if the formula : $C4<>"" is true, for the range C4: last cell in row C4.

Thanks for your Help

[madmaxxx89]

I always get the Error:
"Run-time error '5':
Invalid procedure call or argument"