Macro Help

[Kim]

I've recorded a macro but it doesn't run how I want them:

Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Accept", _
TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Reject", _
TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Consider", _
TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

I've tried to change to Range("AA2:AA" & lngRow) but it still doesn't work.
Can anyone help?

 

[Gary''s Student]

Should work if lngRow is settup properly:

Dim lngRow as Long
lngRow = 123
Range("AA2:AA" & lngRow).Select

 

[Billy Liddel]

Assuming that the list is dynamic, you are adding rows to the data then you
need to find what the lastrow is each time. Adapt this which closely follows
your code.

Sub LastRowNumber()
Dim LastRow As Long

Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
LastRow = Selection.Rows.Count + 1
MsgBox LastRow
End Sub

HTH
Peter

 

[Kim]

I have already set it up for other formulas. Every time I tried to run, it
doesnt work.
Keep showing "With Selection" part as error.

I got previous formulas. Not sure that would help

strFormula9 = "=IF(Z2>=Accept,""Accept"",IFZ2>=Consider,""Consider""_
,""Reject""))"
Range("AA2:AA" & lngRow).Formula = strFormula9

Regards,
Kim