Displaying certain coloured cell

F

fomula problems

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
 
P

paul

if the cells are colored with conditional formats you can use the same
condtions to countif or sumif...if you have coloured manually you will have
to wait till somone posts a link to the solution....
 
B

Bob Phillips

What do you mean exactly by displayed?

--
HTH

Bob Phillips

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

fomula problems

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

Bob Phillips

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

fomula problems

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

Bob Phillips

where?

--
HTH

Bob Phillips

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

fomula problems

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
 
B

Bob Phillips

The code I gave you already does just that.

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

--
HTH

Bob Phillips

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

fomula problems

Cheers Bob for your help

Bob Phillips said:
The code I gave you already does just that.

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

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
Click to expand...
 
Top