Conditional Format entire row

**Dean81** · 03-22-2012, 06:40 AM

I have the following code to highlight cells within a user defined date range. At present it highlights any cell that meets the criteria. I would like it to highlight the entire row. Does anyone know how to do this?

Sub Auto_Open()
'
'
Dim MyInput As String
Dim MyInput2 As String

MyInput = InputBox("Enter Start of Date Range dd/mm/yyyy", "Start Date")
MyInput2 = InputBox("Enter End of Date Range dd/mm/yyyy", "End Date")

Cells.Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:=MyInput, Formula2:=MyInput2
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1").Select
End Sub

**venkat1926** · 03-23-2012, 01:21 AM

why not this macro perhaps simpler CHANGE THE COLOR NUMBER IF YOU WANT

Sub test()
Dim MyInput
Dim MyInput2
Dim cfind1 As Range, cfind2 As Range
MyInput = InputBox("Enter Start of Date Range dd/mm/yyyy", "Start Date")
MyInput2 = InputBox("Enter End of Date Range dd/mm/yyyy", "End Date")
Set cfind1 = Cells.Find(what:=CDate(MyInput), lookat:=xlWhole)
Set cfind2 = Cells.Find(what:=CDate(MyInput2), lookat:=xlWhole)
Range(cfind1, cfind2).EntireRow.Interior.ColorIndex = 3
End Sub

**Dean81** · 03-23-2012, 06:19 AM

Thanks venkat1926,

When I try that I get an error masseage saying "Method 'Range' of object '_Global' failed" any idea why that might be?

Thanks,

Dean