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]

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?

How is the 'Refresh' achieved in normal circumstances? Is it via a macro or is it manual?

If it is manual, perhaps you could turn it into a macro (macro recorder should give you some starting code if needed) then use something like this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MacroToRefreshData()<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> PreExit<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">'Put the code that does the refresh here</SPAN><br>    <br>PreExit:<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Turning 'EnableEvents' off while the refresh is done should stop everything being coloured during the refresh.


Another possible approach:
If the refresh updates values in other columns as well as column I then you could try using the existing code but change

If Not IChanged Is Nothing Then

to

If IChanged.Address = Target.Address Then

So the main code that transfers the value to column N and colours the row should only get actioned if the change to the sheet is confined to column I only.

 

[tlc_in_OK]

Thanks Peter. . .the second option of limiting it to edits that occur in column "I" works perfectly for my situation.