Macro to create dynamic range

[Worijaa]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have several cells that a need to name with reference to an offset formula to create a dynamic range. Instead of manually punching the names, I have created a macro. But it is not functioning properly. <o></o>
<o></o>
What I have done so far:<o></o>
<o></o>
I my excel sheet, column A contains the offset formula that the cell name should refer to and column D contains the name of that range. When I am using following code, I got the wrong reference in the formula<o></o>
The desire result is dynamic named range e.g. Date1 with reference to =OFFSET(Charts!J19;0;0;1;Charts!$M$3)<o></o>
<o> </o>
Appreciate if anyone could help<o></o>
-----<o></o>
<o> </o>
Sub AddName()<o></o>
Dim RngIndx As String<o></o>
Dim Strname As String<o></o>
Do Until ActiveCell.Row = 500<o></o>
If Selection.Interior.ColorIndex = 36 Then<o></o>
Strname = ActiveCell.Offset(, 3)<o></o>
ActiveWorkbook.Names.Add (Strname), RefersTo:=ActiveCell.Formula<o></o>
End If<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Loop</st1lace><o></o>
End Sub<o></o>
<o></o>