Highlight Row Based On Condition-So close
 

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub

Highlight Row Based On Condition-So close
 

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).SetFirstPriorit y
With FormatRng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub

Regards,
Per

On 21 Jan., 22:31, Rookie_User
wrote:
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.FormatConditi ons.Count).SetFirstPriori*ty
* * With Selection.FormatConditions(1).Interior
* * * * .PatternColorIndex = xlAutomatic
* * * * .Color = 65535
* * * * .TintAndShade = 0
* * End With
End Sub

Highlight Row Based On Condition-So close
 

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGrid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Rookie_User" wrote in message
...
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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub

Highlight Row Based On Condition-So close
 

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
--
Cheers,
Ryan


"Rookie_User" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End Sub