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]

Hi Peter,

this is the complete code I am trying to use

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range, BChanged As Range, HChanged As Range
          
    Set BChanged = Intersect(Target, Columns("B"))
    
    If Not BChanged Is Nothing Then
     Application.EnableEvents = True
        For Each rCell In BChanged
            If UCase(rCell.Value) = "VOID" Then
                With rCell.Offset(, 1).Resize(, 15)
                    .Value = "VOID"
                    With .Interior
                        .Pattern = xlGray16
                        .PatternColorIndex = xlAutomatic
                    End With
                End With
            End If
        Next rCell
      Application.EnableEvents = False
    End If
    
    Set HChanged = Intersect(Target, Columns("H"))
    
    If Not HChanged Is Nothing Then
    Application.EnableEvents = True
        For Each rCell In HChanged
            If UCase(rCell.Value) = "WP" Then
                With rCell.Offset(, -6).Resize(, 22)
                    With .Interior
                        .ColorIndex = 15
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                    End With
                End With
             End If
        Next rCell
        Application.EnableEvents = False
        
    End If
    
End Sub

and I find that none of the intended events occur. can you see what the error is?

Robert

 

[Peter_SSs]

Hi Peter,

this is the complete code I am trying to use

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range, BChanged As Range, HChanged As Range
          
    Set BChanged = Intersect(Target, Columns("B"))
    
    If Not BChanged Is Nothing Then
     Application.EnableEvents = True
        For Each rCell In BChanged
            If UCase(rCell.Value) = "VOID" Then
                With rCell.Offset(, 1).Resize(, 15)
                    .Value = "VOID"
                    With .Interior
                        .Pattern = xlGray16
                        .PatternColorIndex = xlAutomatic
                    End With
                End With
            End If
        Next rCell
      Application.EnableEvents = False
    End If
    
    Set HChanged = Intersect(Target, Columns("H"))
    
    If Not HChanged Is Nothing Then
    Application.EnableEvents = True
        For Each rCell In HChanged
            If UCase(rCell.Value) = "WP" Then
                With rCell.Offset(, -6).Resize(, 22)
                    With .Interior
                        .ColorIndex = 15
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                    End With
                End With
             End If
        Next rCell
        Application.EnableEvents = False
        
    End If
    
End Sub

and I find that none of the intended events occur. can you see what the error is?

Robert

Yes, you have your EnableEvents back to front. The first one in each section should be False and the last one True (refer back to post #10). After fixing that, you'll need to run the little macro again to enable them.

 

[Robert E Lee]

Hi Peter

running the 3 line macro solves the problem (now that my Falses and Trues are the right way round) however, when this routine is used live, the operators will not have the means to go into the VB environment to run code in the immediate window, so I guess the sub routine should be called from the main body of code, if that is the case where should the call go?

And, one final question, could you tell me the correct format for amended the font, I tried

With Selection.Font
        .FontStyle = "Bold"
        .Size = 8
        .ColorIndex = 1
        
    End With

which failed

Robert

 

[Peter_SSs]

... when this routine is used live, the operators will not have the means to go into the VB environment to run code in the immediate window, so I guess the sub routine should be called from the main body of code, if that is the case where should the call go?

Once you have the code right 'Events' should not be left in the disabled state so the other users should have no need to do anything with VBA.

And, one final question, could you tell me the correct format for amended the font, I tried

With Selection.Font
        .FontStyle = "Bold"
        .Size = 8
        .ColorIndex = 1
        
    End With

which failed

On its own, that code works fine for me.

In what way did it fail for you? (error mesage, wrong formatting, nothing,...?)

Why are you using 'Selection' in your code? It is rarely required and slows your code. Note that nothing in the codes I suggested use 'Selection' in them. Remember also that 'Selection' was used in error in your recent code and I got you to remove it.

 

[Robert E Lee]

Hi Peter

I think I finally understand the 'Events' process.

The error message I receive when I try the

With .Font
                      .FontStyle = "Bold"
                      .Size = 8
                      .ColorIndex = 1
 End With

is Run time error 438

Object doesn't support this property or method

Robert

 

[Peter_SSs]

I forgot to ask exactly which line was causing the error but try replacing

.FontStyle = "Bold"

with

.Bold = True

If that doesn't solve the problem, can you post more of the code so we can see what the 'With .Font' is applying to?

 

[Robert E Lee]

Here is a section of the code

If UCase(rCell.Value) = "VOID" Then
                With rCell.Offset(, 0).Resize(, 16)
                    .Value = "VOID"
                    With .Interior
                        .Pattern = xlGray16
                        .PatternColorIndex = xlAutomatic
                      With .Font 'error message occurs on this line
                      .Bold = True
                      .Size = 8
                      .ColorIndex = 1
                      End With
                    End With
                End With
            End If

Robert

 

[Peter_SSs]

After your "With .Interior" you don't have an "End With" before you then do "With .Font".
In that case the ".Font" is trying to be applied to ".Interior" but Font isn't a property of Interior, hence the message.

On another matter, ".Offset(,0)" isn't offsetting at all, so can be left out.

So try it like this. The red line has been moved up

If UCase(rCell.Value) = "VOID" Then
    With rCell.Resize(, 16)
        .Value = "VOID"
        
        With .Interior
            .Pattern = xlGray16
            .PatternColorIndex = xlAutomatic
        End With
        
        With .Font
            .Bold = True
            .Size = 8
            .ColorIndex = 1
        End With
            
    End With
End If

Now the ".Font" is applying to the range rCell.Offset(, 0).Resize(, 16) and Font is a property of a range so should be no error.

 

[Robert E Lee]

Thanks again Peter, that is now producing the desired result, sorry to have been such a pain

Robert

 

[tlc_in_OK]

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.