Changing Colours on Worksheet

[Jahson]

Hi

I have a worksheet which is colour coded, Is it possible
to change all cells that are highlighter say yellow to
blue?

Thanks

 

[Bob Phillips]

Jahson,

Select al of the cells to be checked (it doesn't have to be just the
coloured cell;s), and run this code

Dim cell As Range

For Each cell In Selection
With cell.Interior
If .ColorIndex = 6 Then
.ColorIndex = 5
End If
End With
Next cell

note that this will not work for conditionally formatted cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[yogendra joshi]

Following code will select ALL cells with yellow background...

You can now choose to do some other thing with the range instead of
selection or after selection

Sub color_find()
Dim cell, rng As Range

For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
If rng Is Nothing Then
Set rng = Range(cell.Address)
Else
Set rng = Union(Range(rng.Address), Range(cell.Address))
End If
End If
Next cell
rng.Select
End Sub

 

[Tom Ogilvy]

if you have a reference to a range, why convert it to an address, then back
to a range. Additionally, I think this will cause problems for a range with
many areas becuase of string length limitations:

also
Dim cell, rng As Range

dimensions cell as Variant, rng as Range
This is obviously not what you intended

Sub color_find()
Dim cell as Range, rng As Range

For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
rng.Select
End Sub