| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying certain coloured cell
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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.... -- paul remove nospam for email addy! "fomula problems" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying certain coloured cell
Cheers Bob for your help
"Bob Phillips" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| Displaying in a cell the formula from another cell | Excel Discussion (Misc queries) | |||
| up to 7 functions? | Excel Worksheet Functions | |||
Linear Mode
