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
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