Combining 2 Worksheet_change codes

[MikeWip]

hello guys, I have a Worksheet_change code that is working perfectly well (see below). I would like to add another code, using the same functionality i.e. (Worksheet_change) so that all entries in column HA are automatically Uppercase.
How can I add a second code, right after the one below so all entries in column HA are systematically in Upper case?

Thanks guys in advance for your help with that. I am a beginner and I am still learning
cheers!!!
Mike

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo CleanExit:

If Not Intersect(Target, Range("G:G")) Is Nothing Then

Application.EnableEvents = False

If Target.Value = "PROMO" Then
Target.Offset(0, 202).Value = "Y"
Else
Target.Offset(0, 202).Value = ""
End If

Application.EnableEvents = True

End If

Exit Sub

CleanExit:
Application.EnableEvents = True
Err.Clear


End Sub

 

[steve the fish]

Are you saying you also input data manually into column HA because this change event inserts data into column HA doesnt it?

 

[Tim_Excel_]

Me.[HA:HA] = [INDEX(UPPER(HA:HA),)]

Add this line before the Exit Sub line, or before the last End If line if you only want it to execute if you change something in Col G. Let me know if this worked for you.

 

[MikeWip]

that is correct, we can also insert data manually onto column HA... unless the first code gets activated and put the Y in that column HA.

thanks for your help.
Mike

 

[MikeWip]

sorry mate, this code doesn't work.
thanks for the quick reply. I do appreciate it

 

[Tim_Excel_]

sorry mate, this code doesn't work.
thanks for the quick reply. I do appreciate it

Please clarify what happens, tested and working on my end.

I must add that it's for the better to determine the last row in the HA column, that would significantly reduce the line's processing time.

 

[MikeWip]

I think the issue is coming from 'merged cells'.

Can we say, Range("HA7:HA1000") always be uppercase instead of column HA...
my sheet keeps running and running... can't stop.

cheers for your help,

Mike

 

[Tim_Excel_]

[COLOR=#333333]Me.[HA7:HA1000] = [INDEX(UPPER(HA7:HA1000),)][/COLOR]

EDIT

I now notice you do not enable events before making changes in the worksheet. Basically, you made a self-activating macro. The sub activates when something in the sheet is changed, which causes changes to be made, which causes the sub to fire again, which causes changes to be made.... You get the picture

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents= False

Application.EnableEvent= True
End Sub

 

[MikeWip]

THANKS SO MUCH MATE!!! do appreciate your help.

 

[Tim_Excel_]

You're very much welcome