How to repeat a macro in multiple rows of a sheet?

**rgoodman** · 06-25-2013, 04:02 PM

Howdy,

I'm new to the board and new to VBA programming, but I think this should be a very simple questions. The code below works for row 5 of my sheet, but I would like it to work on every subsequent row in the sheet (for like 1000 rows). I assume I need a dim loop, but I can't get it to work. Thanks in advance for the help!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If [B5] = "Startup" Then
[F5, I5:K5].Interior.ColorIndex = 3
Worksheets("Input Log").Range("F5, I5:K5").ClearContents

Else
[F5, I5:K5].Interior.ColorIndex = 0
End If

If [B5] = "Shutdown" Then
[D5:E5].Interior.ColorIndex = 3
Worksheets("Input Log").Range("D5:E5").ClearContents

Else
[D5:E5].Interior.ColorIndex = 0
End If

If [I5] = "Catalyst Malfunction" Or [I5] = "" Then
[M5].Interior.ColorIndex = 0

Else
[M5].Interior.ColorIndex = 3
Worksheets("Input Log").Range("M5").ClearContents
End If

Application.EnableEvents = True
End Sub

**tehneXus** · 06-25-2013, 04:23 PM

Hi and welcome to the forums,

hard to say without test data but try (untested):

Please Login or Register  to view this content.

**rgoodman** · 06-26-2013, 10:15 AM

I appreciate the help. Since I'm new to this, I'm not really sure what the code you gave me is doing, but now none of the rows are working properly. I've attached my spreadsheet with my original code--hopefully that helps. Please let me know if you have any questions

**rgoodman** · 06-26-2013, 02:09 PM

TTT, hoping for help

**tehneXus** · 06-26-2013, 02:18 PM

Hi,

try this sample: Worksheet.xlsm

contains:

Please Login or Register  to view this content.

**rgoodman** · 06-26-2013, 02:27 PM

Thanks tehneXus, it's almost there now! The only issue I have now is that I would like for it to still clear the contents in the red cells if the user tries to input anything in them; it's not doing that anymore

**rgoodman** · 06-26-2013, 03:02 PM

It clears the contents if they are typed in before the selection is made, but not if the user types something in after the selection is made; I'd like it to do both

**tehneXus** · 06-26-2013, 04:09 PM

try

Please Login or Register  to view this content.

**rgoodman** · 06-26-2013, 04:17 PM

Thanks again for the help. The issue with the most recent code is that after the startup or shutdown selection is made in the first (fifth) row, the same boxes are set to red in each subsequent row. That is, if you select startup in the first row, every row after that has the same boxes set to red, regardless of if startup or shutdown is selected. Also, if "startup" is selected, "what corrective actions were taken?" needs to be set to red.

Sorry if I'm being a pain--your help is greatly appreciated

**tehneXus** · 06-26-2013, 04:37 PM

Hi,

that was a mistake from my side, should not post here when I'm drunk, anyway, the following code will check/clear/change-color in the row which is just changed depending on values in column B and I:

Please Login or Register  to view this content.

**rgoodman** · 06-26-2013, 05:04 PM

One last thing: if "startup" is selected, "what corrective actions were taken?" needs to be set to red.

I tried to add ".Cells(i, 13)" to this part
Case "Startup"
With Union(.Cells(i, 6), .Range(.Cells(i, 9), .Cells(i, 11)))
.Interior.ColorIndex = 3
.ClearContents
but it didn't help...

**tehneXus** · 06-26-2013, 05:21 PM

Hi,

Column M is changed depending on the value in column I, how should the logic be then?

**rgoodman** · 06-27-2013, 08:55 AM

If "startup" is selected in column B, column M should be set to red and the data cleared. Also, if "shutdown" is selected in column B, column M should be set to red and the data cleared if anything except "catalyst malfunction" is selected in column I. If "catalyst malfunction" is selected in column I, column M should remain available. Let me know if you have any other questions

**rgoodman** · 06-27-2013, 04:00 PM

Does that answer your question?

**tehneXus** · 06-27-2013, 05:55 PM

try

Please Login or Register  to view this content.

**rgoodman** · 07-01-2013, 09:07 AM

Perfect, thank you so much! Now I need to try doing some things to track inputs, but I'm going to try to figure it out myself. I may post some questions if I can't figure it out, but I'll start a new topic since they won't be related to the questions in this thread. Thanks again!

How to repeat a macro in multiple rows of a sheet?

LinkBack

Thread Tools

Rate This Thread

Display

How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Re: How to repeat a macro in multiple rows of a sheet?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions