Highlighting rows problem

[John Moore]

Hi, I am using the below code to enable me to highlight rows within a
worksheet, it works fine although when I try to insert a new column I get the
error message ( Excel cannot shift non-blank cells off the worksheet" ... I
follow the instructions given and i still cannot add a new column ,,,,,
worked fine before I entered this code, could the code be corrupt / wrong in
some way ,,, how can I fix , any suggestions ?



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS As Long = 256
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't
restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.ColorIndex
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
End Sub

 

[Don Guillett]

You were using up all the columns
Try this instead. It also does NOT wipe out other formatting as yours does.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRng As Range
Set MyRng = Target.EntireRow
Application.EnableEvents = False
On Error GoTo end1
Application.Cells.FormatConditions.Delete
With MyRng
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ROW()=ROW(INDIRECT(CELL(""address"")))"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 1
End With
.FormatConditions(1).Interior.ColorIndex = 36
End With
end1:
Application.EnableEvents = True
End Sub

 

[John Moore]

Hi Don ,,, thanks ,, works fine ,, but I still can't add new columns. Could
there be something else wrong?

 

[John Moore]

If I take the code out I can add columns no problem, so I guess the code will
only work if the worksheet is set and no more columns need to be added. But
anything anyone can come up with is much appreciated.

 

[Don Guillett]

You probably still have old formatting. Start with a new sheet or delete the
columns beyond your data & SAVE.
Do ctrl+end to check before/after.

 

[Don Guillett]

If all else fails, send me your workbook