The code I gave you already does just that.

Goto the VBIDE (Alt-F11), insert a code module (InsertModule), paste the
code in, and then run the macro.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"fomula problems" wrote in
message ...
HA HB HC HD HE HF
HA7020 HB7020 HC7020 HD7020 HE7020 HF7020
HA7040 HB7040 HC7040 HD7040 HE7040 HF7040
HA7060 HB7060 HC7060 HD7060 HE7060 HF7060
7030 7030 7030 7030 7030 7030
7050 7050 7050 7050 7050 7050
8020 8020 8020 8020 8020 8020
8040 8040 8040 8040 8040 8040
8060 8060 8060 8060 8060 8060
8030 8030 8030 8030 8030 8030
8050 8050 8050 8050 8050 8050
9020 9020 9020 9020 9020 9020
9040 9040 9040 9040 9040 9040
9060 9060 9060 9060 9060 9060
9030 9030 9030 9030 9030 9030
9050 9050 9050 9050 9050 9050
10020 10020 10020 10020 10020 10020
10040 10040 10040 10040 10040 10040
10060 10060 10060 10060 10060 10060
10030 10030 10030 10030 10030 10030
10050 10050 10050 10050 10050 10050
Pick any of the cells as coloured

"Bob Phillips" wrote:

where?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"fomula problems" wrote in
message ...
Cheers Bob for the help I'm sure that it is the formula, have enclosed
a
sample of my spreadsheet, would you put the formula in and have it
display
results in sheet 2.
Thanks for help.

"Bob Phillips" wrote:

No, it is relatively simple

Sub GetColouredCells()
Dim i As Long
Dim cell As Range

With Worksheets("Sheet1")
For Each cell In .UsedRange
If cell.Interior.ColorIndex = 3 Then
i = i + 1
Worksheets("Sheet2").Cells(i, "A").Value =
cell.Address
Worksheets("Sheet2").Cells(i, "B").Value =
cell.Value
End If
Next cell
End With

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing
direct)

"fomula problems" wrote
in
message ...
The spread sheet has so many cells and takes ages to go through
and
then
make
a note of the ones that are coloured is it possible to getthe
different
coloured displayed on a seperate spreadsheet with the location
that
they
come
from or is that too complicated.

"Bob Phillips" wrote:

What do you mean exactly by displayed?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing
direct)

"fomula problems"
wrote
in
message
...
I have a spreadsheet and certain cells are coloured to
represent
certain
criterier, I would like to display all of one coloured cells
to
show
status
of that colour, say I want all the red cells locations to be
displayed
so
that i can check the status.
Alan