The attached workbook does what you describe. It uses the Worksheet_Change event for Sheet 1 to capture changes in the target column. I substituted your "Gold" and "Silver" routines with a Print for testing. Uncomment and add whatever modification routine you need. If you have any questions let me know. Tom
On Sun, Nov 16, 2008 at 3:02 PM, <[EMAIL PROTECTED]> wrote:
>
> I'm trying to create a macro that will do the following:
>
> When the user changes a value or values in column 'B', the macro will
> iterate through all cells in columns 'C' to 'AO' but only in the same
> row(s) that were changed in column 'B'.
>
> So basically, whatever row or rows got changed, those same rows but
> columns C to AO will be altered. The macro is actually just applying
> some simple formatting to those cells.
>
> Here's what I have so far and it fails on 'theRow = Target.Rows(i)':
>
> Excerpt
> *****************************************************************
> Dim theCell As Range
> Dim theRow As Range
> Dim i As Integer
>
> For i = 1 To Target.Rows.Count
>
> theRow = Target.Rows(i)
>
> For Each theCell In Intersect(Range("C1:AO35"), theRow)
>
> If theCell.Value > 0 Then
>
> 'Is a multiple of 6 - GOLD
> If theCell.Value Mod 6 = 0 Then
> Call setGold(theCell)
> Else
>
> 'Is a multiple of 3 but not a multiple of 6 - SILVER
> If (theCell.Value Mod 3) = 0 Then Call setSilver
> (theCell)
>
> End If
>
>
> End If
>
> Next theCell
> Next i
>
> ****************************************************************************
>
> Why can't I just iterate through a Rows collection?
>
> Any help would be appreciated!
>
> Mark
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
ColChange.xls
Description: MS-Excel spreadsheet
