Combining Two Scripts

[BazzaBoy]

I have one script which converts cell entries to upper case and goes like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TestRangeString As String
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    TestRangeString = "B8:B100,G8:J100"
    If Not Application.Intersect(Range(TestRangeString), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True

    Dim TestRangeString2 As String
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler2:
    TestRangeString2 = "C8:C100,D8:D100,E8:E100"
    If Not Application.Intersect(Range(TestRangeString2), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler2:
    Application.EnableEvents = True
End Sub

I have another script that highlights the row and column of the selected cell, and goes like this:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

How do I combine the two scripts so that both the features work?

Thanks in advance.

[shg]

Combine them to do what? One works when a cell on the sheet changes, and the other when the selection changes. When do you want them both to run?

[BazzaBoy]

All the time.... I suppose.

Sorry shg, I am not a programmer, so I am not getting what you mean. I don't even know, if they need to be combined. If I use them as they are in the worksheet code for sheet 1, I get an error 1004.

I just want them to do what they each do.... all the time.

Regards.

[shg]

If I use them as they are in the worksheet code for sheet 1, I get an error 1004.

Under what circumstances? What line gives the error?

[BazzaBoy]

If I use both scripts together (one below the other) in a fresh workbook as follows:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

-------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TestRangeString As String
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    TestRangeString = "B8:B100,G8:J100"
    If Not Application.Intersect(Range(TestRangeString), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True

    Dim TestRangeString2 As String
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler2:
    TestRangeString2 = "C8:C100,D8:D100,E8:E100"
    If Not Application.Intersect(Range(TestRangeString2), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler2:
    Application.EnableEvents = True
End Sub

then all works well.

But if I use them in one of my existing workbooks, it gives me an error in the line ".ColorIndex = 20". The prompt says: "Unable to set the ColorIndex property of the Interior class" (see attached).

FYI, I have two sheets in the workbook. sheet1 is labelled 'Members' and sheet2 is labelled 'Analysis'.

I don't understand why both features will work in a fresh workbook, but not in my existing workbook.

[snb]

Start reading the code and try to understand what is does and what it is meant for.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Static rr
  Static cc

  If cc <> "" Then
    Application.Union(Columns(cc), Rows(rr)).Interior.ColorIndex = xlnone
    application.enableevents=False
    cells(rr,cc).value=ucase(cells(rr,cc).value)
    application.enableevents=True
  Else
    rr = target.Row
    cc =target.column
    With Application.Union(Columns(cc), Rows(rr)).Interior
       .ColorIndex = 20
       .Pattern = xlSolid
    End With
  End If
End Sub

[BazzaBoy]

Sorry snb,

Your script does not work. Not even in a fresh worksheet.

However, I have done some more digging and found that I am getting an error because the worksheet is protected. If the workshett is not protected, then everything works well. But I need my worksheet to be protected. So I'll just forget about running this script.

Is there any other way, I can know which cell is selected?

[shg]

Your code can unprotect the worksheet, the reprotect it when it's done. See Help for the Protect method.

[BazzaBoy]

Thanks shg,

Yes, I can do that. But I also noticed that while it highlights the selected cell, it also removes background colours from all other cells. That is not good, as some of my heading cells have been assigned background colours.

If somehow, I can define a range in the script, then maybe it would be worth unprotecting and re-protecting the sheet before and after every move.

[snb]

Only a slight modification:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Static rr
  Static cc

  If cc <> "" Then
    Application.Union(Columns(cc), Rows(rr)).Interior.ColorIndex = xlNone
    Application.EnableEvents = False
    Cells(rr, cc).Value = UCase(Cells(rr, cc).Value)
    Application.EnableEvents = True
  End If
  rr = Target.Row
  cc = Target.Column
  With Application.Union(Columns(cc), Rows(rr)).Interior
     .ColorIndex = 20
     .Pattern = xlSolid
  End With

End Sub

[BazzaBoy]

Thanks snb,

It works now, but how do I prevent the script from removing background colours from my heading cells. I would like this script to affect cells A8:AE100 only. Where can I define that range in this script?

[snb]

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Static rr
  Static cc

  If cc <> "" Then
    Intersect(Application.Union(Columns(cc), Rows(rr)), Range("A8:AE100")).Interior.ColorIndex = xlNone
    Application.EnableEvents = False
    Cells(rr, cc).Value = UCase(Cells(rr, cc).Value)
    Application.EnableEvents = True
  End If
  rr = Target.Row
  cc = Target.Column
  With Intersect(Application.Union(Columns(cc), Rows(rr)), Range("A8:AE100")).Interior
     .ColorIndex = 20
     .Pattern = xlSolid
  End With

End Sub

[BazzaBoy]

Thank you very much snb,

It's all working very well now. Thanks again for your input and your help.

You can mark this thread as 'solved'.

Regards.

[snb]

I can't, you can (and must, if ...)