Highlighting rows problem

  • Thread starter Thread starter John Moore
  • Start date Start date
J

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
 
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
 
Hi Don ,,, thanks ,, works fine ,, but I still can't add new columns. Could
there be something else wrong?
 
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.
 
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.
 
Back
Top