Changing Colours on Worksheet

J

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
 
B

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)
 
Y

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
 
T

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
 
Top