Conditional Formatting & range finder help!!

[springy2003]

Hi, I was wondering if you can help me with a problem. It might sound confussing, so bear with me. I want to be able to use conditional formatting without actually selecting it from the Format menu at the top of the page. I know you can record it, but here comes the confussing bit.

I want to be able to find a range, without using the
Range("A1&quot.Select and at the same time highlight the data in a column, again not using Range("A1:A2&quot.Select. When highlighted, I want to conditional format it, so that the highlight data will now be formatted. The macro I have got at the moment is this!

Sub Macro1()

Dim minrow, maxcol


Sheets("sheet1&quot.Select
Range("D13&quot.Select
Selection.Copy

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop

Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

minrow = ActiveCell.row
maxcol = ActiveCell.Column
ActiveCell.Offset(-3, 0).Select
MsgBox minrow & vbNewLine & maxcol


End Sub

'Range("L10&quot.Activate
'Selection.FormatConditions.Delete
'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
' Formula1:="=$L$13"
'With Selection.FormatConditions(1).Font
' .Bold = True
' .Italic = True
' .ColorIndex = 3
'End With
'End Sub

But as you can see, I recorded this, and it uses the terms like range("A1&quot.select, which I don't want since when I update and write data into another column, I want the macro to be able to spot where the new data has been inputed on the data sheet and be able to conditional format it. Do you understand or do I have to explain it better????

 

[Zathras]

Here is some fundamental code you can build on:

Option Explicit

Sub test()
  SetConditionRed ActiveSheet.Range("D13")
End Sub

Sub SetConditionRed(CellToSet As Range)
' Given a cell as a range, set conditional formatting
' such that this cell is compared to the cell 3 to the right
' and displays in red if Less than or Equal to that cell's value.

With CellToSet
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=" + .Offset(0, 3).Address
   With .FormatConditions(1).Font
     .Bold = True
     .Italic = True
     .ColorIndex = 3
   End With
End With
End Sub

 

[springy2003]

OK, I have figured some stuff out now. Now I am having problems highting a select range. Without using Range(A1:A3) for example, I would like to be able to find it without naming the range. Is there anyway in doing so. Maybe a way of highlighting the last column with figures and number in.