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

 

[Robert E Lee]

Peter

thank you for all your help so far. I understand all the points you make and appreciate the reasons behind them. However the new code you suggest does not place "VOID" in the appropriate cells, which seems odd, so I went back to my original, poorly designed, code, which did function as desired (within the limitations you indicated), but now this does not function either. The Enable Events command is still in place so can you think of any other reason that might have compromised your code?

Thanks

Robert

 

[Peter_SSs]

However the new code you suggest does not place "VOID" in the appropriate cells,

If not in the appropriate cells, then where does my new code place them?
For example, if you type "Void" into cell B100, where does my code place the results?

The Enable Events command is still in place

What do you mean by that? If the events have become disabled, they are not re-enabled just by having a command 'in place'. You must manually do that again in the Immediate Window as I described before or have another little macro like this that you can run manually.

Sub EE()
    Application.EnableEvents = True
End Sub

 

[Robert E Lee]

Peter

typing "VOID" into B100 has no effect on the worksheet.

When I said that 'Application.EventsEnabel= True' was still in place, I meant that the Immediate Window still shows that command.

Robert

 

[Peter_SSs]

typing "VOID" into B100 has no effect on the worksheet.

Then almost certainly your events are disabled.

When I said that 'Application.EventsEnabel= True' was still in place, I meant that the Immediate Window still shows that command.

As I explained in my last post having that 'in place' or 'showing' does nothing to ensure events are enabled.

Either ...

a) Place your cursor in the Immediate Window at the end of the 'Application.EnableEvents=True' line and press Enter to action it again, or

b) In the Immediate Windoe on a new line type Application.EnableEvents=True
again and press Enter, or

c) Paste the 3 line macro I posted last time in a standard module and run it.

... then try typing Void in a column B cell again.

 

[Robert E Lee]

Once again, Peter, thank you very much for the time you have spent on this. Running the 3 line macro solved the problem.

Regards

Robert

 

[Peter_SSs]

Once again, Peter, thank you very much for the time you have spent on this. Running the 3 line macro solved the problem.

Regards

Robert

No problem.

So now you have the code from post #10 targetting just column B, but allowing multiple cell changes and ingoring upper/lower case entry all working okay?

 

[Robert E Lee]

Hi Peter

I hope you won't mind my revisiting this series of posts. The code works perfectly but I have 3 questions regarding extending it

1) is there any reason why it would not work in a 97-2003 workbook?

2) I would like to add some formatting, again in a 2003 workbook, along the lines of

With rCell.Offset(, 1).Resize(, 15).Selection.Interior        
        .Pattern = xlGray16
        .PatternColorIndex = xlAutomatic
End With

how should I include this?

3) I would like to apply a similar operation to another column, is it possible to make another "Private Sub Worksheet_Change(ByVal Target As Range)" macro, perhaps changing the name to"Private Sub Worksheet_ChangeB(ByVal Target As Range)" or should the new code be added to the existing code. (I wish to do something similar to column H)

Thanks for all your help

Robert

 

[Peter_SSs]

1) is there any reason why it would not work in a 97-2003 workbook?

Not sure about 97 but the existing code certainly works in 2003 for me. If it isn't doing anything, check your EnableEvents again!

2) I would like to add some formatting, again in a 2003 workbook, along the lines of

With rCell.Offset(, 1).Resize(, 15).Selection.Interior        
        .Pattern = xlGray16
        .PatternColorIndex = xlAutomatic
End With

how should I include this?

See my code below, but the red bit above needs to be removed.

3) I would like to apply a similar operation to another column, is it possible to make another "Private Sub Worksheet_Change(ByVal Target As Range)" macro, perhaps changing the name to"Private Sub Worksheet_ChangeB(ByVal Target As Range)" or should the new code be added to the existing code. (I wish to do something similar to column H)

New code needs to be incorporated with the existing code. New structure suggested below.

<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> rCell <SPAN style="color:#00007F">As</SPAN> Range, BChanged <SPAN style="color:#00007F">As</SPAN> Range, HChanged <SPAN style="color:#00007F">As</SPAN> Range<br>    <br><SPAN style="color:#007F00">'Check for changes in col B & do stuff</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> BChanged = Intersect(Target, Columns("B"))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> BChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</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> BChanged<br>            <SPAN style="color:#00007F">If</SPAN> UCase(rCell.Value) = "VOID" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> rCell.Offset(, 1).Resize(, 15)<br>                    .Value = "VOID"<br>                    <SPAN style="color:#00007F">With</SPAN> .Interior<br>                        .Pattern = xlGray16<br>                        .PatternColorIndex = xlAutomatic<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> rCell<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <br><SPAN style="color:#007F00">'Now check for changes in col H & do stuff</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> HChanged = Intersect(Target, Columns("H"))<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> BChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'Similar structure to the 'B' section above</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Robert E Lee]

Thanks a million, Peter; I hope to be able to get back to THAT problem later today and will let you know how I get on

Regards

Robert

PS although a quick confirmation; should

'Now check for changes in col H & do stuff
    Set HChanged = Intersect(Target, Columns("H"))
    
    If Not BChanged Is Nothing Then
        'Similar structure to the 'B' section above
    End If
    
End Sub

be

'Now check for changes in col H & do stuff
    Set HChanged = Intersect(Target, Columns("H"))
    
    If Not HChanged Is Nothing Then
        'Similar structure to the 'B' section above
    End If
    
End Sub

Robert

 

[Peter_SSs]

Yes Robert, sorry about that, your correction is valid. (Note to self: Be more careful with Copy/Paste!)