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


--
JamieMorien
------------------------------------------------------------------------
JamieMorien's Profile: http://www.excelforum.com/member.php...o&userid=19499
View this thread: http://www.excelforum.com/showthread...hreadid=398943

Select all cell with the same format
 

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-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=398943

Select all cell with the same format
 

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-®ex" wrote in message
...

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-®ex
------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=398943

Select all cell with the same format
 

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

--
JamieMorie
-----------------------------------------------------------------------
JamieMorien's Profile: http://www.excelforum.com/member.php...fo&userid=1949
View this thread: http://www.excelforum.com/showthread.php?threadid=39894

Select all cell with the same format
 

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...

:)

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


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=398943

Select all cell with the same format
 

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

Select all cell with the same format
 

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

--
JamieMorie
-----------------------------------------------------------------------
JamieMorien's Profile: http://www.excelforum.com/member.php...fo&userid=1949
View this thread: http://www.excelforum.com/showthread.php?threadid=39894

Select all cell with the same format
 

Hi Jamie,
You can get the color from the VBE Help look for colorindex

Also from my page on color
http://www.mvps.org/dmcritchie/excel/colors.htm


"JamieMorien" wrote ...
Excellent it works fine...my problem was getting the right colour code.