I have several merged cell ranges (unlocked) on a protected sheet that I want to spell check, respectively, based on the color index. Problem is 1) that it checks all these ranges and 2) after it is done checking, a message pops up asking 'Do you want to continue checking at the beginning of the sheet?' to which if you select 'yes', it will check the entire sheet (which is locked by the way)!!! Spell check for single cells I guess doesn't work the same for merged cells?
Questions are:
How do I spell check merged cells only?
How do I get rid of that message at the end (what ever happened to 'Spell check complete')?
ActiveSheet.Unprotect
If Range("A6:A8").Interior.ColorIndex = 16 Then
ActiveSheet.Range("G33:P36").CheckSpelling
ElseIf Range("A9:A13").Interior.ColorIndex = 16 Then
ActiveSheet.Range("W33:AF36").CheckSpelling
ElseIf Range("A14:A17").Interior.ColorIndex = 16 Then
ActiveSheet.Range("AM33:AV36").CheckSpelling
ActiveSheet.Range("BC33:BL36").CheckSpelling
ElseIf Range("A18:A24").Interior.ColorIndex = 16 Then
ActiveSheet.Range("BS33:CB36").CheckSpelling
ActiveSheet.Range("CI33:CR36").CheckSpelling
ElseIf Range("A25:A30").Interior.ColorIndex = 16 Then
ActiveSheet.Range("CY33:DH36").CheckSpelling
ActiveSheet.Range("DO33:DX36").CheckSpelling
ElseIf Range("A31:A34").Interior.ColorIndex = 16 Then
ActiveSheet.Range("EE33:EN36").CheckSpelling
End If
ActiveSheet.Protect
A workaround of this issue is to Unmerge
and after spell checking Merge
the cells. In this case at the end of the range the Spell checker will not show the pop-up.
For the merged cells use this:
(assume that DO33:DX36 is the merged range)
ActiveSheet.Range("DO33:DX36").UnMerge
ActiveSheet.Range("DO33:DX36").CheckSpelling
ActiveSheet.Range("DO33:DX36").Merge
In this case the popup is not shown.
To check if a cell is merged Range("P39").MergeArea.Cells.Count>1
return TRUE
if the cell (P39) is cell of a merged range.
Also can be used Range("P39").DisplayFormat.MergeCells
or directly Range("P39").MergeCells
which returns a Boolean
value.