Conditional formatting #17

Hi,

I need to format a row or column based on the value a cell in that row.  
How could i do that? i.e. If the value of cell A1 is less than or equal to  
0 u want the color pattern of B1 to H1 to change into green.

tnx in advance,
eqbal



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
0
news6816 (13)
11/3/2004 9:58:27 AM
excel 39879 articles. 2 followers. Follow

7 Replies
366 Views

Similar Articles

[PageSpeed] 20

Usually conditional formatting is done based on the value of that cell.
In this case, use the drop down box to change from "Cell Value is" t
"Formula is".  Select a cell and use the Toolbar Format - Conditiona
formatting.  You need to write a formula with a logical (ie. TRUE o
FALSE) response.  In this case, in row 1 the formula to trigger th
conditional format would be

=A1<=0

The format can then be set as you choose.  the conditional format cn b
copied down and the formula is relative (as with formulae in cell) s
use $ signs as appropriate

--
gatwickx
-----------------------------------------------------------------------
gatwickxx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1561
View this thread: http://www.excelforum.com/showthread.php?threadid=27481

0
11/3/2004 1:08:29 PM
> =A1<=0
>
> The format can then be set as you choose.  the conditional format can be
> copied down and the formula is relative (as with formulae in cell) so
> use $ signs as appropriate.

To color the entire row  then ALL of your cell references
would have absolute  column addressing.   i.e.     =$A1<= 0

There is no need to copy Conditional Formatting down,  simply
select your column(s)   or the entire worksheet (Ctr+A) before
entering your Conditional Formatting.   You can use the Format
Painter,  and you can use the fill-handle to fill down everything, but
it is best to set it up the way you want at the beginning.

More information on Conditional Formatting, on my site:
  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



0
dmcritchie (2586)
11/3/2004 4:30:41 PM
HI,

I used your comments so far as i understood them. But there is one problem  
and that is i did not ask my question correctly:

actually i want to conditionally format my entire columns B to G according  
to the value of their relative leftmost cell in column A in each row. i.e.  
if the value of a1 is less than or equal to 0 then b1 to g1 becom blue or  
...., and if the value of a2 is less than or equal to 0 b2 to g2 turn into  
blue. if i use absolute references then all the conditions would refer to  
a1, if i don't and select b1 to g1 and then use the conditional formatting  
then the condition in c1 is automatically relative to b1 (that is if b1<=0  
then ...). now please tell there is another way other than conditionally  
formatting each cell from b1 to g1 relative to a1 and then using the fill  
handle or format painter for the other rows below.

tnx,
eqbal

On Wed, 3 Nov 2004 11:30:41 -0500, David McRitchie <dmcritchie@msn.com>  
wrote:





-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
0
news6816 (13)
11/3/2004 6:09:51 PM
You would use  $A1   if the active cell is on Row 1 when you
enter your Conditional Formatting formulas.

I don't think you looked at my web page   condfmt.htm

Select A1  so you are at the top of the worksheet.

Select  Columns  B:G   since these are the only columns
you want to be colored.     That means that B1 will be your
active cell.  Since the formula is absolute to column ($A), it really
only matters that you are on row 1 when you enter the C.F.

Format, Conditional Formatting
condition 1:
    formula is:   =$A1 <= 0
         format button,  pattern,  choose a light blue
    =$A1 <= 0           B:G become blue

You are set up completely no  C.F  formula need to be
copied to any other cells,  no fiddling with the Format Painter.
---
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

"Eqbal Vkilzadeh" <news@kala-ravan.com> wrote in message news:opsgwh2pe1w6rab0@eqbal...
> HI,
>
> I used your comments so far as i understood them. But there is one problem
> and that is i did not ask my question correctly:
>
> actually i want to conditionally format my entire columns B to G according
> to the value of their relative leftmost cell in column A in each row. i.e.
> if the value of a1 is less than or equal to 0 then b1 to g1 becom blue or
> ..., and if the value of a2 is less than or equal to 0 b2 to g2 turn into
> blue. if i use absolute references then all the conditions would refer to
> a1, if i don't and select b1 to g1 and then use the conditional formatting
> then the condition in c1 is automatically relative to b1 (that is if b1<=0
> then ...). now please tell there is another way other than conditionally
> formatting each cell from b1 to g1 relative to a1 and then using the fill
> handle or format painter for the other rows below.
>
> tnx,
> eqbal
>
> On Wed, 3 Nov 2004 11:30:41 -0500, David McRitchie <dmcritchie@msn.com>
> wrote:
>
>
>
>
>
> -- 
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/


0
dmcritchie (2586)
11/3/2004 6:27:52 PM
HI,

I used your comments so far as i understood them. But there is one problem
and that is i did not ask my question correctly:

actually i want to conditionally format my entire columns B to G according
to the value of their relative leftmost cell in column A in each row. i.e.
if the value of a1 is less than or equal to 0 then b1 to g1 becom blue or
...., and if the value of a2 is less than or equal to 0 b2 to g2 turn into
blue. if i use absolute references then all the conditions would refer to
a1, if i don't and select b1 to g1 and then use the conditional formatting
then the condition in c1 is automatically relative to b1 (that is if b1<=0
then ...). now please tell there is another way other than conditionally
formatting each cell from b1 to g1 relative to a1 and then using the fill
handle or format painter for the other rows below.

tnx,
eqbal

On Wed, 3 Nov 2004 11:30:41 -0500, David McRitchie <dmcritchie@msn.com>
wrote:





-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
0
news6816 (13)
11/3/2004 8:50:47 PM
Now i get it. So there is a difference between $A and $A$. in your  
previous mail i thought it was a typing mistake.

Thnaks a lot.

On Wed, 3 Nov 2004 13:27:52 -0500, David McRitchie <dmcritchie@msn.com>  
wrote:

> You would use  $A1   if the active cell is on Row 1 when you
> enter your Conditional Formatting formulas.
>
> I don't think you looked at my web page   condfmt.htm
>
> Select A1  so you are at the top of the worksheet.
>
> Select  Columns  B:G   since these are the only columns
> you want to be colored.     That means that B1 will be your
> active cell.  Since the formula is absolute to column ($A), it really
> only matters that you are on row 1 when you enter the C.F.
>
> Format, Conditional Formatting
> condition 1:
>     formula is:   =$A1 <= 0
>          format button,  pattern,  choose a light blue
>     =$A1 <= 0           B:G become blue
>
> You are set up completely no  C.F  formula need to be
> copied to any other cells,  no fiddling with the Format Painter.
> ---
> 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
>
> "Eqbal Vkilzadeh" <news@kala-ravan.com> wrote in message  
> news:opsgwh2pe1w6rab0@eqbal...
>> HI,
>>
>> I used your comments so far as i understood them. But there is one  
>> problem
>> and that is i did not ask my question correctly:
>>
>> actually i want to conditionally format my entire columns B to G  
>> according
>> to the value of their relative leftmost cell in column A in each row.  
>> i.e.
>> if the value of a1 is less than or equal to 0 then b1 to g1 becom blue  
>> or
>> ..., and if the value of a2 is less than or equal to 0 b2 to g2 turn  
>> into
>> blue. if i use absolute references then all the conditions would refer  
>> to
>> a1, if i don't and select b1 to g1 and then use the conditional  
>> formatting
>> then the condition in c1 is automatically relative to b1 (that is if  
>> b1<=0
>> then ...). now please tell there is another way other than conditionally
>> formatting each cell from b1 to g1 relative to a1 and then using the  
>> fill
>> handle or format painter for the other rows below.
>>
>> tnx,
>> eqbal
>>
>> On Wed, 3 Nov 2004 11:30:41 -0500, David McRitchie <dmcritchie@msn.com>
>> wrote:
>>
>>
>>
>>
>>
>> --
>> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
>



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
0
news6816 (13)
11/3/2004 9:14:56 PM
I see you got your answer.  Reposting simply caused your messages
to be refreshed.   Glad it worked out and was understood.


0
dmcritchie (2586)
11/3/2004 9:26:44 PM
Reply:

Similar Artilces:

how do I convert date format yyyymmdd to mm/dd/yyyy
how do I convert date format yyyymmdd to mmddyyy I have rows of dates displayed as yyyymmdd (ie 20100131) I want them displayed as regular dates (ie 01/31/2010) If it's a column of cells... Select the column Data|text to columns (in xl2003 menus) choose fixed width, but don't have any delimiter lines Choose Date (ymd) This will convert the data to dates. Now you can format the ranyge the way you like. Datahead wrote: > > how do I convert date format yyyymmdd to mmddyyy > > I have rows of dates displayed as yyyymmdd (ie 20100131) > > I wan...

conditional formulas
Please, I need help with these formulas . . . 1.) if worksheetC!C12 says "1" then I want worksheet D!E18 to say "1" , but if C!C12 says "2" then I want D!F18 to say "2" 2.) if worksheet G!C12 says "G", thenD!I31 should say "G", but if G!C12 says "F", then I want D!H31 to say "F" 3.) if A!C12 says "930-12" and A!D12 says either "U" or "L" then D!E2 should say "U" or "L", but if A!C12 says "12-230" & A!D12 says "U" or "L" then D!F2...

Date formatting
I am doing a spreadsheet on products with expiration dates. When I enter 7/08, indicating that the product will expire in July 2008, the data in the cell comes up Jul-04. All I want is the month and year. I have tried several format cell commands, as they relate to date. But the only way I have found to get it in the cell correctly is to type 7/1/08. It then comes up Jul-08. Isn't there a way to just type month and year?? --- Message posted from http://www.ExcelForum.com/ Hi mkingsley! Afraid not! You've noticed that 7/04 is interpreted as 4th July of the current year (US Se...

formatting excell
How do I make an entire row change to a new color if one cell in that row gets any data entered into it? Select your range. I selected A1, then selected the whole worksheet (ctrl-a (twice in xl2003)) Then with A1 the activecell. format|conditional formatting I used a formula of: =counta(1:1)>0 and formatted the pattern nicely. Vstar1969 wrote: > > How do I make an entire row change to a new color if one cell in that row > gets any data entered into it? -- Dave Peterson ec35720@msn.com ...

Numeric Cell Format
I regularly copy a table from Excel to a text file that has fixed width I always have to adjust the numbers, however. Can I specify a numeric format so that 1,000.00 is displayed like this and that any preceding zeros would be spaces. eg, the following list of numbers would always line up to the right in a fixed width font 1,000.00 100.00 10.00 0.00 Eddie assuming the values you need to copy are in column A you could add another column with the following formula =REPT(" ",8-LEN(TEXT(A1,"0.00"))) & TEXT(A1,"#,##0.00") and copy down. you w...

time format #5
hi, I have this huge amount of data in which the ime format is like :00:12 and I need to add zero's in trhe begining i.e. 0:00:12. I have tried all the time formats and nothing works can someone recommend anyway of doing it? Hi see your other post. P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany "Himanshu" <ascool_asice@yahoo.co.in> schrieb im Newsbeitrag news:18ec701c41b8e$3a2a8e70$a101280a@phx.gbl... > hi, > > I have this huge amount of data in which the ime format is > like :00:12 and I need to add zero's in trhe beginin...

Date Format in Word 2007
I created a letter and I want to create a date to read MM/dd/YYYY. It is not showing in my drop down list? Lucy The dates available are dictated by the language format at the cursor. In the top right corner of the insert date dialog, there is a window from which you can select available languages. The US English language will provide the date style you require. OR If you are creating a letter template, add a Createdate field eg {CreateDate \@ "MM/dd/yyyy"}. OR If you simply want the date at the cursor, a simple plan is to add a macro to the QAT (Quick Access Toolba...

Conditional Formatting Lost
Running Excel 2000 in Windows ME. When I right click and choose INSERT which inserts a row above a row that has conditional formatting about 1/2 the time the conditional formatting is lost in the row below the newly inserted row. What causes this and if not known could you suggest a workaround? Also is there a way to force the formatting on newly inserted rows? --- Message posted from http://www.ExcelForum.com/ ...

Conditional Formating #9
I have an Excel spreadsheet that has C3=4, C4=3, & C5=2 and I want to do contitional formating for another cell to have an arrow showing a direction based on C3 - C5 (e.g., if B3>C3 arrow up, if B3=C4 nothing, if B3<C5 arrow down). Is there any way to do that? I can make a color of the cell but would like to have a symbol. Any help would be appreciated. Thanks Bob -- Robert G. Flade, RN, MS Director - Emergency Departments The Hospital of Central CT at New Britain General & Bradley Memorial 100 Grand Street/PO Box 100 New Britain, CT 06050-0100 so "the other cell...

How do you change the preset borders on the Formating Tool bar?
There are preset borders available to use on the Formatting Tool bar and some of them have changed from standard lines to thick lines and I don't know how I did it or how to change them back? Just click them on or off. Please hit yes if my comments have helped. Thanks. "wfmcguigan" wrote: > There are preset borders available to use on the Formatting Tool bar and some > of them have changed from standard lines to thick lines and I don't know how > I did it or how to change them back? My border tool on Formatting Toolbar has both regular ...

Zero % change to "-" custom number format
We are working with an add-on product that exports data into Excel. The product exports raw data into our spreadsheet and we do the formatting. The problem we have is that we want to replace 0% with a dash "-". I have tried a few things in Cell Format with custom numbers, but without any luck. We are looking for a custom number format not a macro to resolve our issue Thanks for your help in advance. David 0%;-0%;- Regards Trevor "David" <anonymous@discussions.microsoft.com> wrote in message news:2B917FC5-B22E-4AC1-A25F-F0B4E030F260@microsoft.com... > We ...

Controls tab missing from "Format Controls" for Option Button
Excel 2007 under Vista I am trying to set the "Cell Link" of a group of Option Buttons, but when I right-mouse | Format Controls on the option button the "Controls" tab is missing. When I insert the control, I have the option to use Forms or ActiveX type controls, but in either case the following is inserted "=EMBED("Forms.OptionButton.1","")" It looks like Forms type controls are added in regardless of whether I select the Forms or ActiveX type. Any clues? > When I insert the control, I have the option to use Forms or ActiveX typ...

Formatting Anomaly
I've asked Excel to format all of the cells in a column with Wrap Text, and for the most part it does just that. But I've found that when the text is too long, it fills the cell with '#'. Is there any way to get Excel to wrap the text without regard to how much there is? Excel 2002 (10.6501.6714)SP3 Thanks. I don't think it's the wrap text that's the problem. Try formatting the cell as General. (Text format shows ### when you have a string between 255 and 1024 characters long in that cell.) Christopher Weaver wrote: > > I've asked Excel to f...

Conditional formatting on Clipart/Shapes
Hi, I am trying to be little creative in trying to present our financial statements pictorially and conditional format it depending on how we stand against Budget. I would like a Clipart or Shapes in the form of a human body. The head represents the Rev or Net Revenue, the body represents the Costs/Opex and the legs represents the Margin and Margin %. The body can be bifurcation into smaller parts which can represent different cost elements. Depending on how we stand against budget, i would like the body colour (different parts of the body) to be coloured in Red or Green via conditional fo...

Excell
Can anyone help - Im finding that if i Cut an entire row which has cells in it that hve Conditional Formatts and Insert that row elsewhere in a workbook that the conditional formatts get all messed up, this seems to be a glich with Excel 2000 - does any one know of any fixes - it doesnt do the same think in Excel XP. Please email me if you can help or know how i can ask microsoft direct without it costing me a fortune Jim, My guess is that the Conditional Formatting is using a formula rather than a cell value. It may be the relative/absolute cell references in the formulas. Give de...

Format Axis
I have a line chart which is pulling data from a different sheet in my workbook. The X axis is the date, formatted: Jan-XX, Feb-XX, etc. The Y axis is dollar amounts formatted as 200000, 400000, etc. - increasing by 200K at each interval. - this is correct. The X axis is defaulting to date the same date all the way across, "Jan-00, Jan-00, Jan-00". So, I'm seeing Jan-00 12 times, where I would like to see each month of the year. When I change the X axis to so that it reads each month, as I'd like... The scale on my Y axis changes to: min = 0 (this is correct) ma...

COleDateTime::Format and language
Hello everybody. I'm using COleDateTime::Format() with "%B %Y" (full month name and year) and it gives me the month name in English, I need this name in same language than Windows (Spanish in my case), how can I do this? or COleDateTime::Format() always gives the names in English? Thanks in advance, William GS Are you running on a Spanish build of Windows? If so, you should get the appropriate language. If you are running under English Windows I believe you will have to built the time yourself using your own strings from localized resources. You can check the locale to...

copy conditional formatting
1. I want to copy the conditional formatting set to cell A1 to Cell B1, C1, D1..K1.How can I do so? Ajit Hi Ajit, Use the format painter(paint brush icon). Select the cell that has the desired cond. formatting, then press the format painter(double click if copying format to 2+ cells), select the cell(s) that you want the same cond. formatting applied to. Have a great day! -- Kevin Lehrbass www.spreadsheetsolutions4u.com "ajitbmunj" wrote: > 1. I want to copy the conditional formatting set to cell A1 to Cell B1, C1, > D1..K1.How can I do so? > > Ajit You cou...

enhanced conditional formatting
i want a conditional formatting system that is not limited like the current one. currently i can only set 3 conditions and the formating settings for each condition being true in the following scenario i would require 6 conditions that would require the values of more than 1 row/column to be of a specific value to create the desire effect. they are as follows: 1) if drivable is = yes and elapsed between 0 and 6 cell shadow green 2) if drivable is = no and elapsed between 0 and 3 cell shadow green 3) if drivable is = yes and elapsed between 6 and 9 cell shadow orange 4) if drivable is = no...

More than 3 outcomes (formats) with conditional formatting
I think I know the answer to this already (i.e., use worksheet_change event instead of conditional formatting), but anyway, I'd like to have a groups of cells be colored up to 8 different ways based on 8 different conditions. Is there any way (other than the one mentioned above) to do this with conditional formatting? Thanks! Hi short answer: no :-) -- Regards Frank Kabel Frankfurt, Germany David Jenkins wrote: > I think I know the answer to this already (i.e., use > worksheet_change event instead of conditional formatting), > but anyway, I'd like to have a groups ...

Format text field as number with +/-
I am using Word 2007. I inserted a text form field, using the legacy forms controls. Under properties for that field, I formatted it as Type: Number, and Number Format: 0.0 I want the field to display the number with the +/- sign (e.g., if I enter "1.5" it displays as "+1.5", and if I enter "-1.5" it displays as "-1.5"). Zero should be displayed as "0.0" I found information about using switches on Graham Mayor's site, but I couldn't get the switch to work as expected. This is what I tried: { FORMTEXT \# +0.0 } Than...

Access 2003
I have formatted my field to currency and it always rounds up to the nearest dollar but i need to include cents. i've tried changing the amount of decimal places but nothing seems to work. thanks in advance! Marie, Make sure that your field is defined as Numeric - Single or Double. If it's Byte, Integer, or Long Integer, it will not accept decimal values. -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.net/~cccsolutions "Find a job that you love, and you'll never work a day in your life." "mariee...

Formatting numbers in a TextBox
I have a form with several subforms. Some of the subforms have TextBoxes for which numeric data will be entered. I'd like to format the way such data shows up after the user hits <Enter> such that: 1. All decimal places typed in are shown but no terminal zeros are displayed. 2. All numbers less than 1 show a leading zero. 3. All integer entries do not display a decimal point. Examples: If 1.250 is entered, then 1.25 is displayed. If .25 is entered, then 0.25 is displayed. If 12.00 is entered, then 12 is displayed (no decimal point). If 12.0010 is entered, then 12.001 is dis...

Conditional Format on Date Match
I am keeping track of my vehicle mileage and fuel used. I have a table of dates where I keep my mileage. I have a table of dates to track my fuel. All on the same sheet. On the date that I get fuel, I want the (date matching) cell in th mileage table to change format (background red). I can get the conditional format to work, if I input a constant or pic cells where the date matches. What I am having trouble doing is getting the format to change from th LIST (column) of dates so all I have to do is input my fuel date and i will change the format of the same date in my mileage table. T...

Confusion...(conditional formatting)
Thanks to all that replied to my post!! I was looking to make F2 field a required field, i.e <red> If E2 = "yes" then make F2 a required field. I am going to email this spreadsheet to users, if the user select "yes to question asked in E column, I would like them to provide detaile information in column F. Does that make sense? Thank you again for your help! this forum rocks -- nishapurohi ----------------------------------------------------------------------- nishapurohit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3098 View this...