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

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

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

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.

"John Moore" wrote:

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

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

If all else fails, send me your workbook

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"John Moore" wrote:

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