Change style of merged cells

[jkmblogs]

This should be easy but has me stumped.

I'm trying to change the cell styles.
It works fine unless the cell is merged and then I get various errors such as "Wrong number of arguments or invalid property assignment" also run-time error 450 depending on how I was trying to achieve the assignment.

I coded the following as an example but don't know how to handle merged cells... what I tried below doesn't work.

Sub ChangeStyle()
Dim vNewStyleName As String
Dim oSh As Worksheet
Dim oCell As Range
vNewStyleName = "Title"
For Each oSh In ActiveWorkbook.Worksheets
For Each oCell In oSh.UsedRange.Cells
If oCell.MergeArea.Cells.Count = 1 Then 'not merged
oCell.Style = "Normal"
oCell.Style = vNewStyleName
Else 'Merged
With oCell.MergeArea.Cells(1, 1)
.Style = "Normal"
.Style = vNewStyleName
End With
End If
Next
Next
End Sub

 

[rollis13]

I think that with this you would achieve the same result since you are using UsedRange:

Option Explicit
Sub ChangeStyle()
    Dim vNewStyleName As String
    Dim oSh    As Worksheet
    Dim oCell  As Range
    vNewStyleName = "Titolo"                      '<-change style name as per language used
    For Each oSh In ActiveWorkbook.Worksheets
        oSh.UsedRange.Style = "Normal"
        oSh.UsedRange.Style = vNewStyleName
    Next
End Sub

 

[jkmblogs]

I think that with this you would achieve the same result since you are using UsedRange:

Option Explicit
Sub ChangeStyle()
    Dim vNewStyleName As String
    Dim oSh    As Worksheet
    Dim oCell  As Range
    vNewStyleName = "Titolo"                      '<-change style name as per language used
    For Each oSh In ActiveWorkbook.Worksheets
        oSh.UsedRange.Style = "Normal"
        oSh.UsedRange.Style = vNewStyleName
    Next
End Sub

Thanks but I stupidly omitted a step... I only intended to perform for certain cells (not whole used range).
Revised code (producing error) below :

Sub ChangeStyle()
    Dim vNewStyleName As String
    Dim oSh As Worksheet
    Dim oCell As Range
    vNewStyleName = "Title"
    For Each oSh In ActiveWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style = "Title 3 2" Then
                If oCell.MergeArea.Cells.Count = 1 Then             'not merged
                    oCell.Style = "Normal"
                    oCell.Style = vNewStyleName
                Else                                                'Merged
                    With oCell.MergeArea.Cells(1, 1)
                        .Style = "Normal"
                        .Style = vNewStyleName
                    End With
                End If
            End If
        Next
    Next
End Sub

 

[jkmblogs]

I think that with this you would achieve the same result since you are using UsedRange:

Option Explicit
Sub ChangeStyle()
    Dim vNewStyleName As String
    Dim oSh    As Worksheet
    Dim oCell  As Range
    vNewStyleName = "Titolo"                      '<-change style name as per language used
    For Each oSh In ActiveWorkbook.Worksheets
        oSh.UsedRange.Style = "Normal"
        oSh.UsedRange.Style = vNewStyleName
    Next
End Sub

Again, thanks for feedback... inspired me to have another look.
It works if I use

                    With oCell.MergeArea           'instead of With oCell.MergeArea.Cells(1, 1)
                        .Style = "Normal"
                        .Style = vNewStyleName
                    End With

 

[jkmblogs]

Arrgh... thought I had it but my actual code loops through all cells then passes a range variable to another sub for processing

Both efforts below produce the same error at the "DoIt" line:
Run-time error '424':
Object required


1st Attempt
Dim oCell As Range
DoIt (oCell.MergeArea)


2nd Attempt
Dim oMergedCell As Range
Set oMergedCell = oCell.MergeArea 'oCell being a merged cell
DoIt (oMergedCell)

 

[rollis13]

I can't be of any other help since I don't know your whole project but I think you're almost on the right track now.