Highlight Row Based On Condition-So close

[Rookie_User]

I currently have the below to highlight the cell based on a condition of
comparing a date in column (I) and if it's greater than 90 days past then
highlight the cell - what I need to do is highlight the row from columns A-J,
any idea's?

This has to be programmed and if there is a better structure then I am open
to it.


Sub MarkThem()
Range("I1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=NOW()-90"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub

 

[Per Jessen]

Hi

This should do it (not tested):

Sub MarkThem()
Dim FormatRng As Range
Set FormatRng = Range("I1", Range("I1")).EntireRow
FormatRng.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLessEqual, _
Formula1:="=NOW()-90"

FormatRng.FormatConditions
(FormatRng.FormatConditions.Count).SetFirstPriority
With FormatRng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub

Regards,
Per

 

[Bernard Liengme]

The following worked for me in XL2010 beta. You may need to change the
parameters for .PatternColorIndex , etc

Sub TryMe()
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range(Cells(1, "A"), Cells(LastRow, "K")).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$I1<TODAY()-90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGrid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
End Sub

best wishes

 

[Ryan H]

I'm not a big fan of changing conditional formatting with code unless you are
developing a new sheet and you don't plan to manipulate the data thru code,
but you still can. So in your case I would just use this. Hope this helps!
If so, let me know, click "YES" below.

Sub MarkThem()

Dim MyRange As Range
Dim rng As Range

Set MyRange = Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row)

For Each rng In MyRange
If rng.Value < Date - 90 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, "J")).Interior.Color =
65535
Else
Range(Cells(rng.Row, "A"), Cells(rng.Row, "J")).Interior.Color =
xlNone
End If
Next rng

End Sub