Select all cell with the same format

Can someone please help me. What I need to do seems simple but I can't find info anywhere.

I want to select all cells that are green for example. How can i do this with VB or an alternative?


Thanks for your help

This can be done if the cells you are trying to select are contiguous....

Range("E5:G10).Select

but if your "green" cells are scattered, e.g. only cells C2, F6 and H7 are green, then... i can't help you with that....

Originally Posted by JamieMorien

Can someone please help me. What I need to do seems simple but I can't find info anywhere.

I want to select all cells that are green for example. How can i do this with VB or an alternative?


Thanks for your help

Dim rng As Range
Dim cell As Range
Set rng = Nothing
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 10 Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
If Not rng Is Nothing Then
rng.Select
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"T-容x" <T-ex.1ub8ui_1124957126.2941@excelforum-nospam.com> wrote in message
news:T-ex.1ub8ui_1124957126.2941@excelforum-nospam.com...
>
> This can be done if the cells you are trying to select are
> contiguous....
>
> Range("E5:G10).Select
>
> but if your "green" cells are scattered, e.g. only cells C2, F6 and H7
> are green, then... i can't help you with that....
>
> JamieMorien Wrote:
> > Can someone please help me. What I need to do seems simple but I can't
> > find info anywhere.
> >
> > I want to select all cells that are green for example. How can i do
> > this with VB or an alternative?
> >
> >
> > Thanks for your help
>
>
> --
> T-容x
> ------------------------------------------------------------------------
> T-容x's Profile:
http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=398943
>

I am an ignoramus. How do I implement this code? I normally just start and stop a macro recording and then paste the code in. Doesn't seem to work. What steps need to lead up to inserting this text? Thanks

Using Bob Phillips' code (or your code, or my code...), go to the VBA editor (I assume, and I'm pretty sure you already know how to do this). Insert a new Module by clicking 'Module' in the 'Insert' menu. A new child window will appear (and you'll notice in the Project Explorer a new entry in the Modules folder). Just type the following in the new window:

Sub ILoveGreen()
'Bob Phillips' code goes here
End Sub

Now, you have a new subroutine called 'ILoveGreen'. To use it, you have to "hook" (is the a right term?) it to a control. Maybe you can add a button in the sheet or create a new toolbar/menu. Then assign to the control (button/menu) the ILoveGreen macro...

Originally Posted by JamieMorien

I am an ignoramus. How do I implement this code? I normally just start and stop a macro recording and then paste the code in. Doesn't seem to work. What steps need to lead up to inserting this text? Thanks

Start recording a macro and then quit recording.

Then you'll have the shell of the macro.

You can use tools|macros|macro
select your "dummy" macro and click the Edit button.

Paste Bob's code after this line:

Sub Macro1()

but before the "end sub" line.

Then back to excel.

select the worksheet you want and
tools|macro|macros
select that macro
and click run.

====

But you'll want to make sure that this line is correct:

If cell.Interior.ColorIndex = 10 Then

Select a cell with the color green background you like.
then back to the VBE (alt-f11)
hit ctrl-g (to see the immediate window)

Type this and hit enter:
?activecell.Interior.ColorIndex

You'll see a number. If it's 10, you're done. If it's not, you'll have to use
that number in that line of code.



JamieMorien wrote:
>
> I am an ignoramus. How do I implement this code? I normally just start
> and stop a macro recording and then paste the code in. Doesn't seem to
> work. What steps need to lead up to inserting this text? Thanks
>
> --
> JamieMorien
> ------------------------------------------------------------------------
> JamieMorien's Profile: http://www.excelforum.com/member.php...o&userid=19499
> View this thread: http://www.excelforum.com/showthread...hreadid=398943

--

Dave Peterson

Excellent it works fine...my problem was getting the right colour code. Thanks

Very clear answer

Originally Posted by Dave Peterson

Start recording a macro and then quit recording.

Then you'll have the shell of the macro.

You can use tools|macros|macro
select your "dummy" macro and click the Edit button.

Paste Bob's code after this line:

Sub Macro1()

but before the "end sub" line.

Then back to excel.

select the worksheet you want and
tools|macro|macros
select that macro
and click run.

====

But you'll want to make sure that this line is correct:

If cell.Interior.ColorIndex = 10 Then

Select a cell with the color green background you like.
then back to the VBE (alt-f11)
hit ctrl-g (to see the immediate window)

Type this and hit enter:
?activecell.Interior.ColorIndex

You'll see a number. If it's 10, you're done. If it's not, you'll have to use
that number in that line of code.



JamieMorien wrote:
>
> I am an ignoramus. How do I implement this code? I normally just start
> and stop a macro recording and then paste the code in. Doesn't seem to
> work. What steps need to lead up to inserting this text? Thanks
>
> --
> JamieMorien
> ------------------------------------------------------------------------
> JamieMorien's Profile: http://www.excelforum.com/member.php...o&userid=19499
> View this thread: http://www.excelforum.com/showthread...hreadid=398943

--

Dave Peterson