On Jun 20, 4:03 pm, Dave Peterson wrote:
You're not really looking at row by row, but in this case, it doesn't look like
that's important:

Sub ChangeColor()
Dim rCell As Range
For Each rCell In Selection.Cells
If rcell.Interior.ColorIndex = 3 Then
rcell.Interior.ColorIndex = 1
End If
Next rCell
End Sub

If you're using xl2002+, you may want to look at all the advanced options
(includes changing format) in the Edit|Replace dialog.





prodeji wrote:

Hi all

I'm reviewing a production system and trying to implement best
practices.

What I want to do is select a row/range of rows; for EACH row, if ANY
cell is color 'x', I want to run a macro to change all the cells in
that row that ARE color 'x' to color 'y'.

This works:

Sub ChangeColor()
Dim rCell As Range
For Each rCell In Selection.Cells
If Selection.Cells.Interior.ColorIndex = 3 Then
Selection.Cells.Interior.ColorIndex = 1
End If
Next rCell
End Sub

(yeah, I know it's primitive)
but only if ALL the cells in the selection/selected row(s) is/are
color 'x'.

Hence if the user selects entire rows rather than concise selections,
as they are likely to do, the macro will not work, because it's more
than likely that only a portion of the selected row(s) will be color
'x' and the rest will have no fill.

What adjustments do I need to make for the macro to have desired
results whether selections are concise or not?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thanks for the prompt response,

I tried it, though, and it doesn't seem to be working; is there
perhaps something I'm still missing...?

Actually this was my original code, but it kept telling me 'Cells'
wasn't defined and I couldn't find a solution:

Sub ChangeColor()
Dim rCell As Range, C As Cell
For Each rCell In Selection.Cells
For Each C In rCell.Cells
If C.ColorIndex = 3 Then
C.ColorIndex = 1
End If
Next C
Next rCell
End Sub

I know I may be making some older heads gnash their teeth here :)