Changing Colours on Worksheet
 

Hi

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

Thanks

Changing Colours on Worksheet
 

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)

"Jahson" wrote in message
...
Hi

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

Thanks

Changing Colours on Worksheet
 

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


Jahson wrote:

Hi

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

Thanks

Changing Colours on Worksheet
 

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

--
Regards,
Tom Ogilvy



"yogendra joshi" wrote in message
...
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


Jahson wrote:

Hi

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

Thanks