Change cell color if...
 

try:

Sub blah()
For Each rw In Selection.Rows
For Each cll In rw.EntireRow.Cells
If cll.Interior.ColorIndex = 3 Then cll.Interior.ColorIndex = 1
Next cll
Next rw
End Sub


This is a quick and dirty solution, it looks at all cells in a row
individually and changes it if required. The rows that it looks at are the
entire rows of whatever cells are selected, even if they're only single cells
on that row. One slight downside is that if the user has selected several
areas (by using the ctrl key while selecting) and one area's rows include
some or all of another area's rows then those rows may be processed more than
once, but if this is a one off process it shouldn't matter.
--
p45cal


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