Formula for a fill color help

I can't figure out how to make a formula that will recognize a color a
a value. More specifically; If b1 is made to be green (color index 4) 
would like c1 to insert the letter Y. I am I'm looking for an I
statement so I can use it throughout the whole workbook. 

The formula if I understand it should kind of look like (in C1):

=If(b1=colorindex4,"Y","")

I'm looking to put a "Y" in c1 if b1 is colored in green.

I would really like to also know how to insert a fill color in an
formula!

Thank you for any of your help and time,

Brya


--
Bryan J
0
1/4/2007 6:52:59 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
517 Views

Similar Articles

[PageSpeed] 1

You can add color and perform tasks with them through code but not with a 
standard function. You can however create a UDF.

-- 
-- 
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Bryan J" <Bryan.J.2jww5u@news.excelbanter.com> wrote in message 
news:Bryan.J.2jww5u@news.excelbanter.com...
>
> I can't figure out how to make a formula that will recognize a color as
> a value. More specifically; If b1 is made to be green (color index 4) I
> would like c1 to insert the letter Y. I am I'm looking for an IF
> statement so I can use it throughout the whole workbook.
>
> The formula if I understand it should kind of look like (in C1):
>
> =If(b1=colorindex4,"Y","")
>
> I'm looking to put a "Y" in c1 if b1 is colored in green.
>
> I would really like to also know how to insert a fill color in any
> formula!
>
> Thank you for any of your help and time,
>
> Bryan
>
>
>
>
> -- 
> Bryan J 


0
jmbundy1 (8)
1/5/2007 1:54:28 AM
Try this sample from my archives:
http://www.savefile.com/files/378485
Count n Sum Cells By FillColor_BobPhillips_ColorIndex_UDF.xls
(nicely rendered, full details)

The sample file contains an implementation of Bob Phillips' ColorIndex
Function from his "Processing Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
and some examples on how to use the UDF in Excel

In the sample file, albeit its not illustrated therein,
you would be able to use IF's along these lines:
=IF(ColorIndex(D2)=4,"Y","")
(above would be as per your intents expressed in the post)

Note: As clarified in Bob's page under "Constraints" section, you would
need to press F9 to force calculate as, quote: " .. change a cell's
colour or the text colour does not trigger the Excel calculate event.
This is not a shortcoming of the UDF, but of Excel's calculation event
... "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Bryan J wrote:
> I can't figure out how to make a formula that will recognize a color as
> a value. More specifically; If b1 is made to be green (color index 4) I
> would like c1 to insert the letter Y. I am I'm looking for an IF
> statement so I can use it throughout the whole workbook.
>
> The formula if I understand it should kind of look like (in C1):
>
> =If(b1=colorindex4,"Y","")
>
> I'm looking to put a "Y" in c1 if b1 is colored in green.
>
> I would really like to also know how to insert a fill color in any
> formula!
>
> Thank you for any of your help and time,
> 
> Bryan
> 
> 
> 
> 
> -- 
> Bryan J

0
demechanik (4694)
1/5/2007 1:56:06 AM
Bryan

Excel cannot do this without VBA code of some type.

See Chip Pearson's site for UDF's for working with colors.

http://www.cpearson.com/excel/colors.htm

With Chip's CellColorIndex UDF your formual would look like

=IF(Cellcolorindex(B1)=4,"Y","")


Gord Dibben  MS Excel MVP

On Thu, 4 Jan 2007 18:52:59 +0000, Bryan J <Bryan.J.2jww5u@news.excelbanter.com>
wrote:

>
>I can't figure out how to make a formula that will recognize a color as
>a value. More specifically; If b1 is made to be green (color index 4) I
>would like c1 to insert the letter Y. I am I'm looking for an IF
>statement so I can use it throughout the whole workbook. 
>
>The formula if I understand it should kind of look like (in C1):
>
>=If(b1=colorindex4,"Y","")
>
>I'm looking to put a "Y" in c1 if b1 is colored in green.
>
>I would really like to also know how to insert a fill color in any
>formula!
>
>Thank you for any of your help and time,
>
>Bryan

0
Gord
1/5/2007 3:08:47 AM
Reply:

Similar Artilces:

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Filling Web Form
Dear freinds Hello, I have written code in vc++ using MFC to fill the Webform using HTTP post method and I'm able to fill the form but I'm facing a problem that I'm unable to fill login name and password. So any one amongst you have any Idea or help then please help me With Regards Sachin ...

Fill Down Excluding First Row
Greetings, I have a spreadsheet where the first row (1) consists of labels that describe the column located below each label. For example, I have calories, fat, carbs, etc. It is desirable to have some of these columns filled in automatically. For example one row might be "calories from fat," "calories from carbs," etc, and so I'm doing a "fill down" on those columns. Unfortunately, the fill downs are completing the columns with the labels. So I'm wondering if it's possible to: (a) Exclude the label row (row 1) from the fill down or, better yet;...

Outlook color scheme
I've seen several different installs of Outlook 2003. Some have an orange interface, some blue, some grey. I'm not talking about Outlook web acess, but the local installed copy. Can anyone explain this? I would like to figure out how to change the interface color scheme. -Brian It uses Windows color schemes. Change those from Display Properties (Control Panel or right click you desktop). --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will ...

Trouble doing a formula for excel
Hi All I have a spreadsheet with the following A1: z:\data/pc32/tsheets\unsorder00039.csv I would like to add 1 too the number to make unsorder00040.csv and so I have try mid,right,left i can't seem to do it Cheers "Jason" <Jason@discussions.microsoft.com> wrote in message news:53AAB904-8595-499F-BF38-8BE00826101C@microsoft.com... > Hi All > > I have a spreadsheet with the following > A1: z:\data/pc32/tsheets\unsorder00039.csv > > I would like to add 1 too the number to make unsorder00040.csv and so > I have try mid,right,left i can't see...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Excel formula #24
What is the formula that brings back a zero for an empty cell instead of 0 0 #DIV/0! Try =if(iserror(formula),0,formula) ************ Anne Troy www.OfficeArticles.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:8392DE7F-0B65-4DEE-87F4-985133BB1976@microsoft.com... > What is the formula that brings back a zero for an empty cell instead of > 0 0 #DIV/0! > ...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

A student figuring a formula for wages
Hello, I am beginner student trying to figure out a formula that calculates how to pay people for the number of hours they work, and at the same time figure out any overtime they may have. The wage is in cell B4, and the # of hours is in cell C4. Overtime is figured at 1.5 of the wage in B4. I must put the end result in F4. The instructor rushed through his presentation, and said to use the "if function". The assignment is due on tuesday and any help with this is greatly appreciated. -Thanks in advance ------------------------------------------------ ~~ Message posted fro...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

Creating formulas to calculate time on a 24 hr time clock
I have to create a spreadsheet that will calculate total hrs worked. I've tried several different ways but I guess I don't know enough about building formulas to actually make the thing work. For example, I used a basic formula D3=A3-B3+IF(A3>B3,1)*12. This works to show the time but not the number of hours worked. If an employee comes in 1/2 hour late, it does not properly calculate this. How can I correct my formula? MW I have tried your formula and it works fine (with start time in B3 and finish time in A3). Format the reult as h:mm and it's OK. Andy "MWI...

Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or higher.either text or cell shade. just so it catches the users attention. im running excel 2000. and i have this currently in the cell that i want to aplly this to: =HLOOKUP(D20,'Hidden Data'!GZ10:HB11,2,0)*MAX(15,E20) have you tried conditional formating? format>conditional formating >-----Original Message----- >I need a cell to change color if the value inside the cell reaches 50 or >higher.either text or cell shade. just so it catches the users attention. >im running excel 2000. and i ...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

Please help a exchange newbie.. Message filtering
I have a contact set up on Exchange 2003 that forwards to a cell phone for alerts generated in our environment. This contact is setup to get notice of several events, all events have separate email address as the "sender" one of the events gets allot of notices. I would like to have this contact not receive mail from the one email address that generated allot of events. I have set up the properties under the exchange general > messaging restrictions, and have authenticated users only and from everyone except the email account I don't want to receive alerts from, BUT when anyon...

fill empty picture frame with picture
I've been trying to make a template. I want to have images boxes which can be filled with an image when needed. I can't work out how to do this. Each time I insert picture or change picture, the picture is just dropped on the page and not into the frame. I want pictures to be the same size, position, stroke etc so that other staff members can easily replace pictures as needed without reformatting every time. Please Help!! It would be helpful to know what version Publisher. Right-click the frame, click change picture. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http:/...

Help me do this
Hi Everyone, I need some help. I want to use Windows Live mail without signing in or having a hotmail or live account. I use gmail and it works fine. I have exported all my contacts from my live.com email and imported all of my contacts so that is not the problem. I want to import my calendar from my live account. I can't find anyway to do this. If I stop signing in of course my calendar is gone. How can I export my live.com email calendar into windows live desktop client so I don't have to sign in anymore? Thx "T-Man" <a@anon.com> wrote in message...

Help?
Ok this is for all the formulae gurus out there. TIA I have a list in a particular column, lets say column A This is a list of dates I have another cell which uses data validation to reference this list. Say B1 In cell B1 the only acceptable entries are that which are in column A Can anyone supply me with a formula for my data validation which will only allow 8 entries. These 8 entries....appearing in the list in B1 will be 8 weeks in advance of the current date. I am currently using the following formula =OFFSET(O2,MATCH(YEAR(NOW()),YEAR(O2:O200),0)-1,,SUMPRODUCT(--(YEAR(O2:O200)=YE...

Need help installing Office 2007
I'm trying to install Office 2007 on my Vista PC. A short way through the installation I get this message: " Cannot Find Proofing.en-us”.... I've searched around: http://www.google.ca/search?hl=en&source=hp&q=setup+cannot+find+proofing.en-us+proof.fr+proof.msi&meta=&aq=1&oq=setup+cannot+find+Proofing.en-us\Proof.fr but I can't get anything to work. Any help or suggestions would be very much appreciated. Thank you. Gary What version of Office 2007. Did you have a Trial version already installed on your PC and did you remove it -- Peter ...