If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Conditional format formulas
Hi,
I'm trying to use conditional formatting formulas to change the fill colour of a large table of cells. The table is automatically populated with job numbers, of which there are many. There are only 3 job types and on the same sheet there is a separate table with a column for "job type" and a column for "job number". I want to use a lookup function in the large table to find the job type and then change the cell colour to suite the job type. I've tried using formulas for conditional formating but the formulas limit you from using certain references. i.e. I want the format of each individual cell in a range to change according to that individual cell's own lookup function. But all i can do is change the format of the whole range of cells according to one cell's lookup function. it would be so easy if you could change a cell's formatting using code in an "if" statement. i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red) |
#32
|
|||
|
|||
Conditional format formulas
You need to write a formula that returns either TRUE or FALSE, so something like this would work:
=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1" and set the fill to red... You, of course, would need either two or three conditions - if you have only three conditions, the default formatting can take care of one condition, and the CF can take care of the other two. HTH, Bernie MS Excel MVP "tyronki" wrote in message ... Hi, I'm trying to use conditional formatting formulas to change the fill colour of a large table of cells. The table is automatically populated with job numbers, of which there are many. There are only 3 job types and on the same sheet there is a separate table with a column for "job type" and a column for "job number". I want to use a lookup function in the large table to find the job type and then change the cell colour to suite the job type. I've tried using formulas for conditional formating but the formulas limit you from using certain references. i.e. I want the format of each individual cell in a range to change according to that individual cell's own lookup function. But all i can do is change the format of the whole range of cells according to one cell's lookup function. it would be so easy if you could change a cell's formatting using code in an "if" statement. i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red) |
#33
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
I am trying to shade the row if a particular cell in the row has a date value
in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? |
#34
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
On Sun, 30 Aug 2009 20:52:01 -0700, Josh
wrote: I am trying to shade the row if a particular cell in the row has a date value in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? You can let the internal engine do it. |
#35
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
FormatCFCondition1Formula is:
=$B$2-NOW()=30 Add Condition2 and 3 But you have a couple of conflicts with the = and = Who do you want to win if the value is 30 Gord Dibben MS Excel MVP On Sun, 30 Aug 2009 20:52:01 -0700, Josh wrote: I am trying to shade the row if a particular cell in the row has a date value in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? |
#36
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
I think the quotation marks around your IF statement are probably
messing you up. =If(($B$2-NOW)=30) =if(($B$2-NOW)0) =If(($B$2-NOW)=30) You will still have the logic issues that Gord pointed out. Ken On Aug 30, 11:52*pm, Josh wrote: I am trying to shade the row if a particular cell in the row has a date value in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? |
#37
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
Ken
Don't need to use IF Also NOW is a function and syntax is NOW() Gord On Mon, 31 Aug 2009 14:11:52 -0700 (PDT), Ken wrote: I think the quotation marks around your IF statement are probably messing you up. =If(($B$2-NOW)=30) =if(($B$2-NOW)0) =If(($B$2-NOW)=30) You will still have the logic issues that Gord pointed out. Ken On Aug 30, 11:52*pm, Josh wrote: I am trying to shade the row if a particular cell in the row has a date value in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? |
#38
|
|||
|
|||
Conditional format formulas
Hi, thanks a million Bernie.
My conditional formatting applies to a number of columns too- different dates.. Your formula applies the format to the whole row. So what if I only wanted to apply the formatting to the cell in that row which wasn't empty. (it will contain the same job number which was used as the lookup value- i.e. A5) I'm running into a similar problem where the cells I want to reference in the conditional formatting formula are the cells that the format actually applies to. e.g: I wish I could do this in my formula: =AND(VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1", NOT(ISBLANK("cells in row 9 to which formula applies"))) my spreadsheet looks much like ms project with jobs on the left, dates along the top and coloured cells where the dates and jobs correspond. Cheers for the help, Tyron "Bernie Deitrick" wrote: You need to write a formula that returns either TRUE or FALSE, so something like this would work: =VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1" and set the fill to red... You, of course, would need either two or three conditions - if you have only three conditions, the default formatting can take care of one condition, and the CF can take care of the other two. HTH, Bernie MS Excel MVP "tyronki" wrote in message ... Hi, I'm trying to use conditional formatting formulas to change the fill colour of a large table of cells. The table is automatically populated with job numbers, of which there are many. There are only 3 job types and on the same sheet there is a separate table with a column for "job type" and a column for "job number". I want to use a lookup function in the large table to find the job type and then change the cell colour to suite the job type. I've tried using formulas for conditional formating but the formulas limit you from using certain references. i.e. I want the format of each individual cell in a range to change according to that individual cell's own lookup function. But all i can do is change the format of the whole range of cells according to one cell's lookup function. it would be so easy if you could change a cell's formatting using code in an "if" statement. i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red) |
#39
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
and just a throwaway.. if you want to evaluate column B of each row
individually, get rid of the $2 and instead use $B2. Otherwise, when you copy the format down the rest of your rows, you'll still be evaluating B2... "Gord Dibben" wrote: FormatCFCondition1Formula is: =$B$2-NOW()=30 Add Condition2 and 3 But you have a couple of conflicts with the = and = Who do you want to win if the value is 30 Gord Dibben MS Excel MVP On Sun, 30 Aug 2009 20:52:01 -0700, Josh wrote: I am trying to shade the row if a particular cell in the row has a date value in one of three categories. What I have done is not working. Statements are below. ="If(($B$2-NOW)=30)" ="if(($B$2-NOW)0)" ="If(($B$2-NOW)=30)" It is not coloring a single cell.Any ideas of better logic to use? |
#40
|
|||
|
|||
Incorporating Cell color fill in an "if" logical function?
So is it now true in Excel 2007, that I have to apply conditional formatting
to each cell individually (in this case). In 2003, I was able to drag the cell formatting down a long list of items where the formulas were relative so the row number changed as I went from cell to cell. Excel 2007 seems to want to lock everyting to a specific cell e.g., $m$4. If this is the case, it is terrible. Perhaps I have missed something. I certainly hope so. "T. Valko" wrote: I am using vista and excel 2007. Try this... Assume the cell in question is A1 Select cell A1 Goto Home tabStylesConditional FormattingManage Rules Click New RuleUse a formula to determine..... Enter this formula in the box: =A1="I" Click the Format button Select the desired style(s) OKOKApply Now, click New Rule and repeat the process for each of the conditions. This was much easier in previous versions of Excel! -- Biff Microsoft Excel MVP "Shawn" wrote in message ... I have a similar question to this thread... I have a cell that contains an if statement that returns either I, II, III or IV depending on criteria. Can I also make the cell turn a different colour for each of these results? e.g. (Red for I, Orange for II, etc.) I used to know how to do this in the old excel, but now I am using vista and excel 2007. Thanks. Shawn "Miguel Zapico" wrote: You can use Format-conditional formating, select "Formula is", and insert the condition that evaluates to true/false in the text box. Hope this helps, Miguel. "George_Sky" wrote: I want to identify a specific cell by highlighting it with a cell fill color when an "if" statement returns a "ture" response. How do I do this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Urgent date/scheduling calc needed | jct | Worksheet Functions | 3 | February 24th, 2006 01:36 AM |
Function syntax to compare cell contents | ES | Worksheet Functions | 2 | May 18th, 2005 03:53 PM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
If/And function help | Milehigh | Worksheet Functions | 5 | January 30th, 2004 04:15 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |