Automatically copying contents of cell

[Robert E Lee]

Hi all

I would like to be able to copy a cell to a particular range of cells in the same row based on its contents, specifically, if Column B contains the word "void" I would like the next 15 cells in that row to contain the same word.

I know that this can be done pretty simply using VBA, but would like to avoid this approach. I wondered if there was an element in conditional formatting that would enable me to define the word "void" as a pattern but can't see such an option

thanks

Robert

 

[Peter_SSs]

This is basically what I was looking for, but instead of pasting a specific word like "void" in a field, I need it to copy/paste the value that is changed in one field to another. I can't use a linked field because the original data is populated from an outside source, and is then used in a pivot table.

Thanks for any help you can give.

Welcome to the MrExcel board!

Some more details would be useful. For example ..

1. Are there particular columns/ranges that you want to monitor? (The code in this thread was checking columns B & H)

2. You want to paste the value that is changed into another field. What other field? Where?

3. What version of Excel are you using?

Tell us more about what you have and what you are trying to achieve.

 

[tlc_in_OK]

Hi Peter-

I'm working in Excel 2007. My worksheet is populated by an external stored procedure and contains a couple of different statuses. As a means of analyzing different business decision scenarios, users need to be able to change the status in a cell in column I, and I need the change to be copied to the cell in the same row in column N on the same worksheet. The changes can be done one at a time, or they may actually change one and copy it down sometimes as well, so I need it to be fairly robust. The number of rows will vary on each update from the stored procedure. These fields dynamically update a graph.

Thanks for the quick reply.
T

 

[Peter_SSs]

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> IChanged <SPAN style="color:#00007F">As</SPAN> Range, rCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> IChanged = Intersect(Target, Columns("I"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> IChanged<br>            <SPAN style="color:#00007F">With</SPAN> rCell<br>                Range("N" & .Row).Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> rCell<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[tlc_in_OK]

Perfect! It was the

With rCell
Range("N" & .Row).Value = .Value
End With

part that threw me. Thanks so much! One other thing. . .is it possible to highlight that row at the same time (and have it override the conditional formatting on the page)?

 

[Peter_SSs]

One other thing. . .is it possible to highlight that row at the same time (and have it override the conditional formatting on the page)?

You could highlight at the same time but it won't override any Conditional Formatting.

1. What column(s) do you have Conditional Formatting on?

2. What column(s) in the row do you want highlighted when the column I/N value changes?

3. Assuming your Conditional Formatting just colours the cell(s), what about, with this procedure, putting borders somewhere, or bolding or changing the colour of the font?

 

[tlc_in_OK]

I have conditional formatting on rows that contain a status that should not be changed by users for evaluation, so it's easy to see which rows are already valid. But if they change one of the other statuses to a "valid" status for evaluation on the graph, it applies the conditional formatting too. Ideally, I'd like it to highlight the row in a different color, so they know which ones were changed and can identify and change them back as needed. I could eliminate the conditional formatting and just highlight the changed rows instead.

 

[Peter_SSs]

I could eliminate the conditional formatting and just highlight the changed rows instead.

You didn't really answer the questions about what columns you wanted coloured, so try this which will colour from column A to column N (14 columns), assuming that you have removed the Conditional Formatting. Just add the extra line (shown in blue) to the existing code.

With rCell
    Range("N" & .Row).Value = .Value
    Range("A" & .Row).Resize(, 14).Interior.ColorIndex = 35
End With

 

[tlc_in_OK]

Perfect, thank you Peter. That's just what I needed. You're the best!

 

[Peter_SSs]

No problem, thanks for the feedback.

Note that if the user changes the status back to what it was, the row will still stay highlighted.

 

[tlc_in_OK]

Yes, they'll have to manage that manually, but I'll make them aware there is no "undo" after a macro is run.

But I just realized that when the data is refreshed using the "Refresh" or "Refresh All" it highlights all rows, so this may not be a good option after all. It doesn't do it if I use the "Refresh" button under "Connections" though, so is there a way to disable the other refresh functions, and put a "button" on the menu that effectively goes through the steps of opening "Connections" and selecting the "Refresh" button there?