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
Microsoft MVP Excel
SalesAid Software

"John Moore" wrote in message
...
Hi Don ,,, thanks ,, works fine ,, but I still can't add new columns.
Could
there be something else wrong?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Moore" wrote in message
...
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