Can a formula determine if a cell is a particular colour?

Hi,

I have an Excel 97 spreadsheet with heaps of rows in it, some of which
have red rather than black text.

Is it possible to have a formula that can show me the rows that are
red so I can then sort on that column.

For example, a formula something like:

 =if(cellcolour(A3)=red,1,0)

Thanks a lot for any help.



0
Tino4588 (9)
1/12/2004 10:38:58 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
351 Views

Similar Articles

[PageSpeed] 3

Tino,

No worksheet formula, but you could create a simple UDF. You could try this
technique that evolved out of previous threads between
Harlan Grove and I. Create a function to get the colorindex of cells, namely

Function ColorIndex(rng As Range, Optional font As Boolean = False) As
Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    If rng.Cells.Count = 1 Then
        If font Then
            aryColours = rng.font.ColorIndex
        Else
            aryColours = rng.Interior.ColorIndex
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If font Then
                    aryColours = rng.font.ColorIndex
                Else
                    aryColours(i, j) = cell.Interior.ColorIndex
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function

and test the cell using

=IF(ColorIndex(A3,TRUE)=3,1,0)


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

<Tino@tino.com> wrote in message news:40032159.5466826@news.clari.net.au...
> Hi,
>
> I have an Excel 97 spreadsheet with heaps of rows in it, some of which
> have red rather than black text.
>
> Is it possible to have a formula that can show me the rows that are
> red so I can then sort on that column.
>
> For example, a formula something like:
>
>  =if(cellcolour(A3)=red,1,0)
>
> Thanks a lot for any help.
>
>
>



0
bob.phillips1 (6510)
1/12/2004 11:14:46 PM
Thanks Bob.

Is a UDF just another name for a macro? A function?  What does it
stand for?

Thanks


On Mon, 12 Jan 2004 23:14:46 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>Tino,
>
>No worksheet formula, but you could create a simple UDF. You could try this
>technique that evolved out of previous threads between
>Harlan Grove and I. Create a function to get the colorindex of cells, namely
>
>Function ColorIndex(rng As Range, Optional font As Boolean = False) As
>Variant
>Dim cell As Range, row As Range
>Dim i As Long, j As Long
>Dim aryColours As Variant
>
>    If rng.Areas.Count > 1 Then
>        ColorIndex = CVErr(xlErrValue)
>        Exit Function
>    End If
>
>    If rng.Cells.Count = 1 Then
>        If font Then
>            aryColours = rng.font.ColorIndex
>        Else
>            aryColours = rng.Interior.ColorIndex
>        End If
>
>    Else
>        aryColours = rng.Value
>        i = 0
>
>        For Each row In rng.Rows
>            i = i + 1
>            j = 0
>
>            For Each cell In row.Cells
>                j = j + 1
>
>                If font Then
>                    aryColours = rng.font.ColorIndex
>                Else
>                    aryColours(i, j) = cell.Interior.ColorIndex
>                End If
>
>            Next cell
>
>        Next row
>
>    End If
>
>    ColorIndex = aryColours
>
>End Function
>
>and test the cell using
>
>=IF(ColorIndex(A3,TRUE)=3,1,0)
>
>
>-- 
>
>HTH
>
>Bob Phillips
>    ... looking out across Poole Harbour to the Purbecks
>(remove nothere from the email address if mailing direct)
>
><Tino@tino.com> wrote in message news:40032159.5466826@news.clari.net.au...
>> Hi,
>>
>> I have an Excel 97 spreadsheet with heaps of rows in it, some of which
>> have red rather than black text.
>>
>> Is it possible to have a formula that can show me the rows that are
>> red so I can then sort on that column.
>>
>> For example, a formula something like:
>>
>>  =if(cellcolour(A3)=red,1,0)
>>
>> Thanks a lot for any help.
>>
>>
>>
>
>
>

0
Tino4588 (9)
1/12/2004 11:35:01 PM
Put this in a regular module

Function whatcolor(x)
whatcolor = x.Interior.ColorIndex
End Function

then where red is 3
=IF(whatcolor(B3)=3,1,0)

-- 
Don Guillett
SalesAid Software
donaldb@281.com
<Tino@tino.com> wrote in message news:40032159.5466826@news.clari.net.au...
> Hi,
>
> I have an Excel 97 spreadsheet with heaps of rows in it, some of which
> have red rather than black text.
>
> Is it possible to have a formula that can show me the rows that are
> red so I can then sort on that column.
>
> For example, a formula something like:
>
>  =if(cellcolour(A3)=red,1,0)
>
> Thanks a lot for any help.
>
>
>


0
Don
1/12/2004 11:52:04 PM
UserDefinedFunction

(a user wrote it.)


"Tino@tino.com" wrote:
> 
> Thanks Bob.
> 
> Is a UDF just another name for a macro? A function?  What does it
> stand for?
> 
> Thanks
> 
> On Mon, 12 Jan 2004 23:14:46 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
> 
> >Tino,
> >
> >No worksheet formula, but you could create a simple UDF. You could try this
> >technique that evolved out of previous threads between
> >Harlan Grove and I. Create a function to get the colorindex of cells, namely
> >
> >Function ColorIndex(rng As Range, Optional font As Boolean = False) As
> >Variant
> >Dim cell As Range, row As Range
> >Dim i As Long, j As Long
> >Dim aryColours As Variant
> >
> >    If rng.Areas.Count > 1 Then
> >        ColorIndex = CVErr(xlErrValue)
> >        Exit Function
> >    End If
> >
> >    If rng.Cells.Count = 1 Then
> >        If font Then
> >            aryColours = rng.font.ColorIndex
> >        Else
> >            aryColours = rng.Interior.ColorIndex
> >        End If
> >
> >    Else
> >        aryColours = rng.Value
> >        i = 0
> >
> >        For Each row In rng.Rows
> >            i = i + 1
> >            j = 0
> >
> >            For Each cell In row.Cells
> >                j = j + 1
> >
> >                If font Then
> >                    aryColours = rng.font.ColorIndex
> >                Else
> >                    aryColours(i, j) = cell.Interior.ColorIndex
> >                End If
> >
> >            Next cell
> >
> >        Next row
> >
> >    End If
> >
> >    ColorIndex = aryColours
> >
> >End Function
> >
> >and test the cell using
> >
> >=IF(ColorIndex(A3,TRUE)=3,1,0)
> >
> >
> >--
> >
> >HTH
> >
> >Bob Phillips
> >    ... looking out across Poole Harbour to the Purbecks
> >(remove nothere from the email address if mailing direct)
> >
> ><Tino@tino.com> wrote in message news:40032159.5466826@news.clari.net.au...
> >> Hi,
> >>
> >> I have an Excel 97 spreadsheet with heaps of rows in it, some of which
> >> have red rather than black text.
> >>
> >> Is it possible to have a formula that can show me the rows that are
> >> red so I can then sort on that column.
> >>
> >> For example, a formula something like:
> >>
> >>  =if(cellcolour(A3)=red,1,0)
> >>
> >> Thanks a lot for any help.
> >>
> >>
> >>
> >
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/13/2004 3:18:01 AM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Can E-mail recipient policy be linked to the OU membership?
Running Exchange 2003 (mixed mode) and Windows 2003 server (Windows 2000 mixed mode): I'd like to create a e-mail recipient policy that is linked to the different OU's that are created on the system. Is it possible to link the policy to a specific OU or do I need to create groups on the AD to control which recipients a policy applies to? Martin Moustgaard Yes, you can create a recipient policy that looks to just a specific OU. It is done through LDAP queries. Here are some references to articles and whitepapers that discuss how to work with recipient policies. 249299.KB.EN-US HO...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

can i download lxce serv.exe
want to open message lxce serv.exe using windows xp ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

how many receivers i can add
i want to use outlook send 2000 thousands emails to 2000 peoples at the same time. can i put them all in the recivers and send them at one time? thank you for you help ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can't configure Outlook Express
I had Outlook express set up for the Money newsgroup, but somehow I lost it. Now I even forgot how to set it up again. I always get the error message saying "server cannot be found". The server name I typed in was microsoft.public.money, and I am not sure what should I type in here Thanks for help In microsoft.public.money, wj wrote: >I had Outlook express set up for the Money newsgroup, but >somehow I lost it. Now I even forgot how to set it up >again. I always get the error message saying "server >cannot be found". The server name I typed in was &g...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

what printers can run Vista
just purchased a dell which came with Vista on it. now my printer (HP 5550 deskjet) won't print. I need to find a new printer that i can hook up to the computer. This is an Access newsgroup. We help with questions related to Microsoft Access - the database application that is part of MS Office Professional. -- Rick B "monroe" <monroe@discussions.microsoft.com> wrote in message news:AC746826-251A-4097-AF88-2805BCDE7DEC@microsoft.com... > just purchased a dell which came with Vista on it. now my printer (HP > 5550 > deskjet) won't print. I need to...

Function to determine Directory Size
I'd like to be able to determine the size of a directory or group of files. Does anyone know of an add-in that has a function whereby I can specify a path and get the size of directory of groups of files as the return value? Ex: =dirsize("C:\Documents and Settings\Bob\My Documents\School") =dirsize("C:\Documents and Settings\Bob\My Documents\School\*.doc") It would also be helpful if there were an option to include subdirectories. -- Thanks, Bob Chmara Argh.... Make that "get the size of a directory or group of files" Bob "Bob Chmara" fatfing...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Recommendations Please: Software That Can Backup Outlook While It is Open/Running
As the subject says. There are lots of packages that will backup the pst, etc. once Outloook is closed, but some products claim to do it while Outlook is open. Any suggestions on products that actually do this and do it properly? If the product can safely backup over the network to a server and can do incremental/update backups throughout the day, I'd really like to hear about it. Thanks. ...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...