worksheet change event and multiple target cells

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "h" Then
    Target.Interior.Color = vbGreen
ElseIf Target.Value = "s" Then
        Target.Interior.Color = vbRed
ElseIf Target.Value = "v" Then
        Target.Interior.Color = vbBlue

End If

I've got my coding working for individual cells but fails when multiple cells are selected. What can be done to fix this.

So, for example I select range A5:A9 and type h all cells of this range should have an h and blue.


Thanks
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Loop through each cell in the Target range.
Also, I like to use Case statements in situations like this. I think they a little cleaner and easier to read and maintain (instead of a whole bunch of ElseIf statements):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    For Each cell In Target
        Select Case cell.Value
            Case "h"
                cell.Interior.Color = vbGreen
            Case "s"
                cell.Interior.Color = vbRed
            Case "v"
                cell.Interior.Color = vbBlue
        End Select
    Next cell
    
End Sub
 

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601
Loop through each cell in the Target range.
Also, I like to use Case statements in situations like this. I think they a little cleaner and easier to read and maintain (instead of a whole bunch of ElseIf statements):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    For Each cell In Target
        Select Case cell.Value
            Case "h"
                cell.Interior.Color = vbGreen
            Case "s"
                cell.Interior.Color = vbRed
            Case "v"
                cell.Interior.Color = vbBlue
        End Select
    Next cell
    
End Sub

Failed, as its not picking up the "range" just first cell of the range selected that's it?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Failed, as its not picking up the "range" just first cell of the range selected that's it?
I don't understand.
Can you tell me exactly what you are doing that should be triggering this event procedure to run.

I think you may be mixing up your events here.
Note that Worksheet_Event procedures (which is what you have here), only runs when cells are updated manually or by copy/paste.
If you want it to run when cells are selected, you would use a Worksheet_SelectionChange event procedure, not a Worksheet_Event one.
 

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601

ADVERTISEMENT

I don't understand.
Can you tell me exactly what you are doing that should be triggering this event procedure to run.

I think you may be mixing up your events here.
Note that Worksheet_Event procedures (which is what you have here), only runs when cells are updated manually or by copy/paste.
If you want it to run when cells are selected, you would use a Worksheet_SelectionChange event procedure, not a Worksheet_Event one.

Ok, I think I see whats going on for the change event.

The case is selected and runs once as programmed. I need it to complete for all additional cells selected. Not having to input the letter and enter key for each selected cell.

The selectionchange event makes no improvement as it stands now.

Does this help?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
The case is selected and runs once as programmed. I need it to complete for all additional cells selected. Not having to input the letter and enter key for each selected cell.
It does, for every single cell that is changed.

I have tested it out and it works for me.
Please explain to me in detail EXACTLY what you are doing. Walk me through an actual example of what you are doing so I can exactly duplicate what you are doing.
 

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601

ADVERTISEMENT

It does, for every single cell that is changed.

I have tested it out and it works for me.
Please explain to me in detail EXACTLY what you are doing. Walk me through an actual example of what you are doing so I can exactly duplicate what you are doing.

Yes I see using the selectionchange event with cells already populated with h it turns all back color when selected. not what im after.

Ok, let me try....

cells or range of cells are blank you then select the example range b3:g3 .
select b3:g3 and type h
all cells in selected range must then have an h and corresponding back color.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
select b3:g3 and type h
all cells in selected range must then have an h and corresponding back color.
There's the problem - doing that only puts an "h" in the first cell in the range, not in all cells in that range.
Try it on a sheet without any VBA, and you will see that doing that only enters an "h" in cell b3 and not in any of the rest.

So, since b3 is the only cell being updated with an h, it makes sense that it is the only see being highlighted, because none of the other cells meet the requirements.
 
Last edited:

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "h" Then
    Target.Interior.Color = vbGreen
ElseIf Target.Value = "s" Then
        Target.Interior.Color = vbRed
ElseIf Target.Value = "v" Then
        Target.Interior.Color = vbBlue

End If

I've got my coding working for individual cells but fails when multiple cells are selected. What can be done to fix this.

So, for example I select range A5:A9 and type h all cells of this range should have an h and blue.


Thanks

Try again, maybe expanded on above.

you have blank worksheet. You select one cell say A1 move cursor over 4 cells A5 which now gives you a range of A1:A5 highlighted (selected). You type h in (which is the A1) of this still selected range.

Now here is what I wish to happen A2 thru A5 will fill with h and back color blue or whatever in case statements Joe laid out.

Note: The select cell or cells will behave as above and the select cell will not have letter in it, user is putting this.

I believe this is a better explanation I hope?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I think I see now. I thought you were saying that all cells in that range were being populated with the value (i.e. through a copy/paste or auto-fill), when in actuality only the first cell in the selected range is being updated, and you want the macro to populate the rest of the cells in the selected range with the same value and apply the color formatting.

Try this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myValue As String
    Dim cell As Range
    
    Application.EnableEvents = False
            
'   Capture value entered in first cell
    myValue = Target.Cells(1, 1).Value
            
'   Set all cells in selection equal to value
    If Selection.Count > 1 Then Selection = myValue

'   Apply appropriate formatting
    Select Case myValue
        Case "h"
            Selection.Interior.Color = vbGreen
        Case "s"
            Selection.Interior.Color = vbRed
        Case "v"
            Selection.Interior.Color = vbBlue
    End Select
    
    Application.EnableEvents = True
            
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top