Select range depending on a column

**Directlinq** · 03-04-2009, 05:33 AM

If i have row 10-20 in column A filled, what vba code would i need to select a range in column C that selects rows 10-20

If that 10-20 rows in column A chnages to say 10-50 the vba code will select 10-50 in column C

Many Thanks

**DonkeyOte** · 03-04-2009, 05:35 AM

Depends on how you're identifying A ... you can use A and then Offset(,2) to select C

Range(Cells(10,"A"),Cells(Rows.Count,"A").End(xlup)).Offset(,2)

The above identifies a range by starting at row 10 in Col A and ending at last non-blank in A ... it then offsets the range by 2 columns - ie resulting in a range in Col C of those dimensions specified by values in A.

**Directlinq** · 03-04-2009, 05:40 AM

That sound like what i want it to do but it is say syntax error when i put it in vba

**DonkeyOte** · 03-04-2009, 05:42 AM

As I said it identifies a range, you need to "do" something with it obviously (ie Set or .Value = "x" etc....), given I can't see your code and have no idea how you intend to use said range I can't comment any further.

**Directlinq** · 03-04-2009, 05:45 AM

Once the cells have been selected i was hoping to apply

Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=TODAY()"
    Selection.FormatConditions(1).Interior.ColorIndex = 46
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D30>0"
    Selection.FormatConditions(2).Interior.ColorIndex = 5
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "= OR(WEEKDAY($B30)=2, WEEKDAY($B30)=3, WEEKDAY($B30)=4, WEEKDAY($B30)=5, WEEKDAY($B30)=6)"
    Selection.FormatConditions(3).Interior.ColorIndex = 39

formatting macro

**DonkeyOte** · 03-04-2009, 05:46 AM

Per Forum rules please Edit your last post and add CODE tags (highlight VBA & press # icon).

Also please specify significance of row 30 ... given this is not an absolute reference I am presuming the formulae you offer are for C30 ?