What is wrong with this code?

[sizemorem]

I want to do conditional formatting on my spreadsheet. My labels are on row 7, columns A:E. I want my conditional formatting to start with row 8. It applies the formatting to row 8 but changes the formula to row 9. I don't understand.

Private Sub ConditionalFormattingForRefundCkReg()
    Selection.FormatConditions.Delete
 
    Range("A8").CurrentRegion.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(AND($E8=""Voided"",$D8>0),TRUE,FALSE)"
    Selection.FormatConditions(1).Interior.ColorIndex = 38
 
End Sub

The formatting that is cell A8 is:
"=IF(AND($E9="Voided",$D9>0),TRUE,FALSE)"

All help is GREATLY appreciated

 

[pgc01]

Hi

Range("A8").[COLOR=blue][B]CurrentRegion[/B][/COLOR].Select

You are selecting the current region and so the labels are also included, making row 8 the second row. That's why you get the 9 in the conditional formatting formula.

You should only select from row 8, down.

 

[sizemorem]

If I use

Range("A8:E", Cells(Rows.Count, 1).End(xlUp).Row).Select

I get an error message about "Method or Object Failed"

Can you suggest another method of selecting an indeterminate range

 

[pgc01]

Try:

Range("A8:E" & Cells(Rows.Count, 1).End(xlUp).Row).Select

 

[sizemorem]

Works terrific now. It is amazing how just small items can mess everything up. I have a LOT to learn.

Thank you so much!!