On Jun 20, 7:46 pm, "Peter T" <peter_t@discussions wrote:
Hi Helmut,

I don't think limiting the cells to check to only those selected within the
usedrange is reliable. Reason - entire rows or columns with same format
extend beyond the usedrange to the edge of the sheet.

Regards,
Peter T

"Helmut Weber" wrote in message

...



Hi prodeji,

I've arrived at the same solution as Dave.

Yet, is was a bit slow,
as it was searching all cells in the selected rows,
so I asked in the german groups for a method
to recude the range to be searched.

Thomas Ramel MVP suggested this:

Application.Intersect(Selection, ActiveSheet.UsedRange)

All combined:

Sub ChangeColor()
Dim oCll As Range ' a cell
Dim rCll As Range ' a range of cells
Set rCll = Application.Intersect(Selection, ActiveSheet.UsedRange)
For Each oCll In rCll.Cells
If oCll.Interior.ColorIndex = 3 Then
oCll.Interior.ColorIndex = 1
Else
If oCll.Interior.ColorIndex = 1 Then
oCll.Interior.ColorIndex = 3
End If
End If
Next
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"- Hide quoted text -

- Show quoted text -

Hi again all

Thanks for the many and obviously thought out responses.
I stepped away from the computer for a bit and came back and was able
to solve it.
Yes, i am feeling more than a bit sheepish :)
Still, I see some improvements to my newbie solution in the code
suggested by you all.

Many thanks,

prodeji