Conditional Formatting with Relative Reference.

Conditional Formatting with Relative Reference.

Sample workbook:

        A        B        C        D        E        F


1.       4.5    6        7.4        8.3    10        2

2.       1        2        3        4        3        2

3.       1        2        3        4        5        6

4.        6        5        4        3        2        1



Hi,

How can Excel 2003 workbook be designed so that if a value of a cell is 
smaller or greater than the value of the cell, then color of the cell is 
auto formatted ?

Formatting of the cell is as follows: Green if value is greater and Red if 
the value is smaller.

Desired result based on the above sample workbook:

        A            B       C           D            E            F

1.     4.5    Green   Green     Green      Green       Red

2.     1        Green   Green     Green     Red          Red

3.     1        Green   Green     Green    Green       Green

4.     6        Red       Red        Red       Red          Red


Peter


0
swiadek (5)
12/15/2005 7:52:27 PM
excel 39879 articles. 2 followers. Follow

4 Replies
547 Views

Similar Articles

[PageSpeed] 36

Select cell B1 & go to conditional formatting
use 'Cell value is' and 'less than' and then enter =$A1
use the Format button to shade the cell in Red
now add another test and repeat the steps above but use 'greater than'

When you're finished, copy the formatting from B1 to the other cells




"Swiatkowski Peter" wrote:

> Conditional Formatting with Relative Reference.
> 
> Sample workbook:
> 
>         A        B        C        D        E        F
> 
> 
> 1.       4.5    6        7.4        8.3    10        2
> 
> 2.       1        2        3        4        3        2
> 
> 3.       1        2        3        4        5        6
> 
> 4.        6        5        4        3        2        1
> 
> 
> 
> Hi,
> 
> How can Excel 2003 workbook be designed so that if a value of a cell is 
> smaller or greater than the value of the cell, then color of the cell is 
> auto formatted ?
> 
> Formatting of the cell is as follows: Green if value is greater and Red if 
> the value is smaller.
> 
> Desired result based on the above sample workbook:
> 
>         A            B       C           D            E            F
> 
> 1.     4.5    Green   Green     Green      Green       Red
> 
> 2.     1        Green   Green     Green     Red          Red
> 
> 3.     1        Green   Green     Green    Green       Green
> 
> 4.     6        Red       Red        Red       Red          Red
> 
> 
> Peter
> 
> 
> 
0
DukeCarey (494)
12/15/2005 8:05:04 PM
Your numbers are in A1:F4, select this range or select B1:F4 (it makes no 
difference)
In Conditional formatting use "Cell value is", "greater than" , $A1   (not 
$A$1) and format to red
Click Add
For second format use "Cell value is", "less than" , $A1   (not $A$1) and 
format to green

best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Swiatkowski Peter" <swiadek@comcast.net> wrote in message 
news:NLSdndXt1Lg7VDzenZ2dnUVZ_tudnZ2d@comcast.com...
> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
>        A        B        C        D        E        F
>
>
> 1.       4.5    6        7.4        8.3    10        2
>
> 2.       1        2        3        4        3        2
>
> 3.       1        2        3        4        5        6
>
> 4.        6        5        4        3        2        1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is 
> smaller or greater than the value of the cell, then color of the cell is 
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if 
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
>        A            B       C           D            E            F
>
> 1.     4.5    Green   Green     Green      Green       Red
>
> 2.     1        Green   Green     Green     Red          Red
>
> 3.     1        Green   Green     Green    Green       Green
>
> 4.     6        Red       Red        Red       Red          Red
>
>
> Peter
>
> 


0
bliengme5824 (3040)
12/15/2005 8:06:12 PM
 Bonjour, *Swiatkowski Peter*

you said :
>           A          B          C           D          E        F
> 2.       1          2          3            4           3        2
> 2.       1     Green   Green  Green    Red    Red

3<1 ?
2<1?
maybe a mistake ?

Select B1:F4
1st condition :The formula is : =B1<$A1 format : red
2nd condition : The formula is : =B1 > $A1 format : green.

Care :
If the value of one cell in the selected range = the value of the column A, 
what color ?
Green ? change =B1 > $A1 in =B1>=$A1
Red ? change =B1<$A1 in =B1<=$A1


-- 
Bien amicordialement,
P. Bastard 


0
12/15/2005 8:19:30 PM
Hi Peter,
Are we supposed to read an extra pass to find out what
value you are checking to.  Please make things simple to read.

To compare the cells in columns B:H  to the cell in A

Select Cell B1     -- so that B1 will still be the active when you then
Select Columns B:H   normally with B1 active but since $A1
    is used actually anything on row 1 could be the active cell.

Format, Conditional formatting
Formula is (1):    =B1>$A1             format as Green
Formula is (2):    =B1<$A1             format as Red

I expect you want   >=    or else  <=
but you gave nothing in your example for if values are equal.
 Formula is (1):   =AND(NOT(ISBLANK($A1)), B1>=$A1)
 Formula is (2):   =AND(NOT(ISBLANK($A1)), B1<$A1)

To read more about Conditional Formatting see:
   http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Swiatkowski Peter" <swiadek@comcast.net> wrote in message news:NLSdndXt1Lg7VDzenZ2dnUVZ_tudnZ2d@comcast.com...
> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
>         A        B        C        D        E        F
>
>
> 1.       4.5    6        7.4        8.3    10        2
>
> 2.       1        2        3        4        3        2
>
> 3.       1        2        3        4        5        6
>
> 4.        6        5        4        3        2        1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is
> smaller or greater than the value of the cell, then color of the cell is
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
>         A            B       C           D            E            F
>
> 1.     4.5    Green   Green     Green      Green       Red
>
> 2.     1        Green   Green     Green     Red          Red
>
> 3.     1        Green   Green     Green    Green       Green
>
> 4.     6        Red       Red        Red       Red          Red
>
>
> Peter
>
>


0
12/15/2005 8:23:37 PM
Reply:

Similar Artilces:

Format the Cell into a telephone number but using country code
Hello i wonder if anyone knows how to do this i do hope so How do i change a Telephone number in a cell to have the +44 country code in first IE 01753 525681 change this with a format to +1753 525681 so basiclly just dropping the first 0. if i replace 0 with +44 then all the 0 will be replaced on the whole list of numbers if i start the cell +44 it starts a formula does anyone know how to do this Please help !! Precede the entry with an apostrophe when you enter it and the plus sign will not try to convert to a formula. For the values already enterd I assume they are seen as ...

Format "unlocked" cells in a protected sheet
Hey thanks for looking ... I work with some idiots and we have a unified timecard in excel. Anyways I have streamlined the worksheets and would like to protect my cells (formulas) from their reoccurring "whoops". Is there anyway to format (fill with color) a cell which is unlocked but in a protected sheet. I.E. some cells I want to make protected while leaving others fully functional. Thanks Again -- TIM Tom I accomplish this by protecting the cells I need and setting the enabl selection property. The following lines do this; ActiveSheet.Protect userinterfaceonly:=True...

Saving custom number format
1) I am trying to save a custom number format for e.g. ###,###,000.000 But when I open a new file this format is not available. Why doesn't it gets saved? (excel 2002) 2) My home computer has recently been upgraded to the latest version of excel. But I used to hit Alt + F and then C to close a file in earlier versions. This no longer works! I have to hit Enter key after pressing the "C". Is it a bug? or am I missing something? Shantanu Oak Custom formats are saved with the workbook. To have these available for all new workbooks, save a blank workbook with the custom formatti...

How do I stop an pivot report from losing conditional formatting
I have a pivot table that needs updated weekly and everytime I refresh the report it loses its formatting and I start over. This report conditional formatting needed reflects changes in color when collections drop for 3 consecutive months in a row. example Oct Nov Dec Jan 451 390 280 180 the report needs to change color first month drop yellow, second orange and third and additional months that have dropped turn red. Any assistance on how to handle conditional formatting and prevent it from losing when refreshed will be appreciated. ...

Hyperlinks.Delete without losing formatting?
I have javascript function that copies a table from a webpage to an Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count > 0){ for (i...

format a cell for displaying numbers in lakhs
How to format a cell to display numbers in lakhs with a cama separation instead of millions Here is a format that you can use [>=10000000]##\,##\,##\,##0.00�;[>=100000]##\,##\,##0.00;##,#�#0.00 -- HTH Bob Phillips "naga" <naga@discussions.microsoft.com> wrote in message news:C2E7B233-B556-460F-A223-6CDB6718CA61@microsoft.com... > How to format a cell to display numbers in lakhs with a cama separation > instead of millions ...

Relation between IV30300 and Receivers module
I was searching through IV30300 the other day for some transactions, and I noticed that I had a number of entries that tied directly back to the Receivers module. I know that the Receiver Line Item history is maintained in POP30310, so why the effective duplicity between the two tables? Are the records in IV30300 intended to maintain allocation and sold quantities, whereas POP is just purchase history? We're running GP8.04 (please don't laugh :) ), and we make use of Lots in our company; I rely primarily on the IV00300 table for current inventory levels, so maybe that's...

CRichEditCtrl-Why input text has different font format?
Hi, I have used RichEditCtrl instead of EditCtrl in all dialogs (in case there are some advantages). Now I found one problem, when users input/change/paste some text in the ctrl, the font format is different from the existing text font. Why does the system design it in this way? Is there a cure? (There are already a lot of dialog and a lot of RichEditCtrl now.) Thank you a lot. -- Best regards, Kelvin By default RichEditCtrl sets the Font for you similarly it does on copy/paste action. In copy/paste action it copies the data along with the Font information due to which that font is applie...

Date to text format
I have a date in a cell in the following format 12/15/05. However need to make this a true text field. I can do it manually by jus going into that cell and preceding the date with a ' (single quote) s it would be '12/15/05 but I can't copy this down to all the othe cells. Anyone know of an easy way to do this -- nufse ----------------------------------------------------------------------- nufsed's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1926 View this thread: http://www.excelforum.com/showthread.php?threadid=37443 Format/Cells/Text, then ...

Format Text in spreadsheet based on text?
I have a really bad spreadsheet that I am unable to sort or create a list out of without doing a bunch of manual editing so I was thinking that if I could highlight certain text with a different font, color, bold or whatever that would be good enough so I can visually make out the data I need and ignore the rest. Any way of doing that which is simple, quick and a few clicks away? James, You can use conditional formatting to change cell fill colors, font attributes and borders based on the contents of the cell (or other cells, such as in the same row). You have three conditios you can s...

Text box format missing when saved from 2007 to 2003
I have a chart that have text boxes with borders and colored backgound in Excel 2007. When I saved the file to Excel 2003 and opened the file in 2003, the text boxes formats are no longer there. The borders and colored background are missing. How to make the formats translate correctly from 2007 to 2003? Please help. Thanks. Hi Itan, It is a known issue which should be fixed in a future update. We understand the frustration, but I don't have a solution for you now. -- Thanks, Christopher This posting is provided "AS IS" with no warranties, and confers no rights. "...

Excel: wheres menu item to change file format conversion options?
Where in the Excel menus is the menu item to allow changing options for the file format converters? For example, to change the maximum line length for the ".prn" file converter. When you're saving a file as .prn??? If yes, then there is no setting to extend past 240 characters per line. Saved from a previous post: There's a limit of 240 characters per line when you save as .prn files. You have a few choices (try against a copy of your worksheet): I'd either concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&R...

How do I create a new footnote reference mark?
My boss likes to use parentheses around the numbers for footnotes. I would like to know if there is a way I can create a numbering format, using continuous numbering, with parentheses around the numbers? If so, how do I do this? Hi TL, Assuming you're using proper Word footnotes, you could run the following macro periodically: Sub FootnoteBracketer() Dim oFtNt As Footnote For Each oFtNt In ActiveDocument.Footnotes With oFtNt.Reference If .Characters.First.Previous <> "(" Then .InsertBefore "(" If .Characters.Last.Next <> ")&...

Help req: how to conditional count with words in cells based on their formatting
I have a text based spread sheet that counts the number of words at the end of each row. I want to be able to bold selected words and have these excluded from the count at the end of the row e.g. frog cat dog (If cat is bolded then the count at the end of the row is 2 not 3). Thanks in advance. Babs That will require VBA code. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "babs" wrote in message I have a text based spread sheet that counts the number of words at the end of each row. I wan...

What format do I use for a witness statement
What format do I use for a witness statement "What format for a witness statement" <What format for a witness statement@discussions.microsoft.com> wrote in message news:783AC228-B07C-4308-8D26-5D696E213CBF@microsoft.com... > What format do I use for a witness statement Whatever the court in your area requires. See a lawyer for that information or contact the court. We can't help you since all states and countries have different laws and procedures. "What format for a witness statement" <What format for a witness statement@discussio...

Sheet References in a formula
I have the following formula that does exactly what I need - thanks to J.E. McGimpsey. =H48/INDEX (B8:K8,COUNTA (B8:K8)) *100. This works fine on the same sheet but now I need to work between sheets. The H48 reference is on Sheet 2 and the B8:K8 range is on Sheet 1. How do I correctly insert the reference to Sheet 1 in this formula? Thanks. RH Try: =H48/INDEX(Sheet1!B8:K8,COUNTA(Sheet1!B8:K8))*100 In article <1630F290-C2C6-474B-82A6-0A34A3EBBC77@microsoft.com>, "Randy Hunt" <anonymous@discussions.microsoft.com> wrote: > I have the following formula that d...

Win98SE Format
I am about to format my OS drive after many years. Can anyone direct me to a document which will instruct me how to save favorites, address book, emails etc. I have most apps/drivers saved, but am bound to forget something! David David wrote: > I am about to format my OS drive after many years. Can anyone > direct me to a document which will instruct me how to save favorites, > address book, emails etc. > I have most apps/drivers saved, but am bound to forget something! > David FAVORITES Browse to C:\WINDOWS\Favorites, copy the folder and stash i...

Summing with multiple conditions
I asked a question yesterday regarding counting with multiple conditions. The sumproduct answer worked great. Now I need to sum the totals in a column based on 2 conditions. Column A willl equal either x, y or z Column b will equal either a, b or c Column c will be times in hh:mm:ss format What I am trying to do is look at column and and b and where the values are, for instance, x and b, sum the total time in column c where those 2 criteria are met. I tried the following, but the result, comes back as though it is a false result: =IF(AND(JOBS!A:A="x",JOBS!B:B="b"),SUM(J...

Formatting a cell in stones and pounds
Am trying to keep a track of someones weight in imperial weights and measures, ie Stones and pounds, but cannot find a way to format the range. Can anyone help please? Sean O'Sullivan In article <3f559089$1@news1.homechoice.co.uk>, "Sean O'Sullivan" <seanbosullivan@hotmail.com> wrote: >Am trying to keep a track of someones weight in imperial weights and >measures, ie Stones and pounds, but cannot find a way to format the range. >Can anyone help please? Is there a reason for not using the easier to use SI units ? I know what you are implying "I d...

Hide Columns Using Numbered References
I'm trying to hide columns in a sheet using references to "numbered columns" instead of "lettered" columns, but I can't figure out the correct syntax. Someone please help. Thanks Sub TestCount() Dim MyCount MyCount = Range("A1").Value * 2 + 3 MyEndRange = 256 Columns("MyCount:MyEndRange").Hidden = True End Sub Columns(MyCount & ":" & MyEndRange).Hidden = True jrew23@yahoo.com wrote: > > I'm trying to hide columns in a sheet using references to "numbered > columns" instead of "l...

conditional formatting question #8
hello, I have a list of prices from 2 stores with same part numbers. I'd like to format the spreadsheet so if the part price from store 1 is higher than store 2 - make that cell Blue. The lower price (either store 1 or 2) should be Red. I've tried this several times but the colors don't change. I'm using the conditional formatting wizard under the Tools menu. What am I missing? Thanks, Oskar Difficult to guess. Is your price data just in different columns of the same row for the same part number? Is your price data on different sheets of the same workbook? If that&#...

Copying a named range with a changing cell reference
I am writing a macro to copy a sheet from a workbook to a new workbook, it has hiddden rows which i am not copying just values, however, I also want to copy the named ranges to the new sheet, the problem being the cell references change as I get rid of hidden rows and columns. Is there any way to carry over a named range, having it refering to values or cell content rather than actuall cell references................ Hi If you only need the values to be copied, you can use "Paste Special" and select values to be pasted. Regards, Per <jwilkes@sedgman.com.au> skrev i en med...

Report reference combo box text
In an Access 2000 report how can you reference the text value of a combo box on an opened form? You could place the reference in your query if your recordsource of the report is a query. It could look like this [Forms]![formname]![Comboname] Where formname is the name of the form you're referencing to and the comboname the combo on your form. Maurice "Robin9876" wrote: > In an Access 2000 report how can you reference the text value of a > combo box on an opened form? > > I should have mentioned this is for in a label in a report to display the text from the...

format detail field at runtime
i need to format a detail section bound textbox at runtime on a report. i tried using the condition formatting by entering IIf(InStr(Me.Result, "+") > 0, "positive", IIf(InStr(Me.Result, "-") > 0, "negative", Me.Result)) nothing changed also tried this in the deatil section Me.txtResult.Properties(40) = IIf(InStr(Me.Result, "+") > 0, "positive", IIf(InStr(Me.Result, "-") > 0, "negative", Me.Result)) but received an error msg. how is this accomplished? tia, mcnewsxp i fixed my undinding the...

Rule Related Q....
I want to set a rule as follows. When I get an email from a person of interest viz xxx@xxx.com, I want to send one email to my yahoo email Id stating that 'm' from 'xxx'. How to do that? I can see the rule to farward the email message and not sure on how to send email as like notification. Is it possible to set the above rule? I am using Microsoft outlook 2002. Thanks, Hanuman I don't think you can send a separate email other than forwarding. "Hanuman Mishra" <Mishra_Hanuman@rediffmail.com> wrote in message news:e4V9ahtaFHA.2536@TK2MSFTNGP10.phx.gbl... ...