coloring in rows

Hi

Could somebody please help me with the following problem?

How do I set a conditional format that if L5,M5, or N5 = �BAD� the
highlight the row yellow, otherwise leave it the standard non fil
color (clear)

Thanks

Andre

--
kob
-----------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2863
View this thread: http://www.excelforum.com/showthread.php?threadid=48371

0
11/9/2005 11:58:28 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
206 Views

Similar Articles

[PageSpeed] 40

Select Row 5. Format> Conditional Formatting. Formula is:
=OR($L5="Bad",$N5="Bad",$M5="Bad")
Set the required colour format,

Hope this helps
Rowan

koba wrote:
> Hi
> 
> Could somebody please help me with the following problem?
> 
> How do I set a conditional format that if L5,M5, or N5 = �BAD� then
> highlight the row yellow, otherwise leave it the standard non fill
> color (clear)
> 
> Thanks
> 
> Andrew
> 
> 
0
11/10/2005 12:14:30 AM
Select the row, and in CF use a formula of =COUNTIF($L$5:$N$5,"BAD")>0, and
in the pattern tab choose your colour.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"koba" <koba.1y97pz_1131580804.1022@excelforum-nospam.com> wrote in message
news:koba.1y97pz_1131580804.1022@excelforum-nospam.com...
>
> Hi
>
> Could somebody please help me with the following problem?
>
> How do I set a conditional format that if L5,M5, or N5 = "BAD" then
> highlight the row yellow, otherwise leave it the standard non fill
> color (clear)
>
> Thanks
>
> Andrew
>
>
> -- 
> koba
> ------------------------------------------------------------------------
> koba's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28639
> View this thread: http://www.excelforum.com/showthread.php?threadid=483715
>


0
bob.phillips1 (6510)
11/10/2005 12:17:50 AM
Thanks

The formula works but its NQR

It only colors in rows that do not have text in them not the whole row
Can you think why this would be?

Andre

--
kob
-----------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2863
View this thread: http://www.excelforum.com/showthread.php?threadid=48371

0
11/10/2005 12:43:25 AM
Sorry neither one of those formulas worked, same problem as before


-- 
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639
View this thread: http://www.excelforum.com/showthread.php?threadid=483715

0
11/10/2005 12:59:09 AM
It works for me. How did you implement it? Did you mean only colours in 
CELLS that do not have text in them and not whole row?

Regards
Rowan

koba wrote:
> Thanks
> 
> The formula works but its NQR
> 
> It only colors in rows that do not have text in them not the whole row.
> Can you think why this would be?
> 
> Andrew
> 
> 
0
11/10/2005 2:12:17 AM
Hi Rowan

It colors in the whole row except for the cells inwhich i have text or
numbers

In the conditional funtion what did you make it. I put it under �qual
to 

Andrew


-- 
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639
View this thread: http://www.excelforum.com/showthread.php?threadid=483715

0
11/10/2005 3:07:37 AM
Try this again:
Select Row 5.
Format>Conditional Formatting>Delete>Condition 1>OK.
Now change the first Drop Down from "Cell Value is" to "Formula Is". 
Then in the right hand box paste the formula:
=OR($L5="Bad",$N5="Bad",$M5="Bad")
Set your required formatting.

Regards
Rowan

koba wrote:
> Hi Rowan
> 
> It colors in the whole row except for the cells inwhich i have text or
> numbers
> 
> In the conditional funtion what did you make it. I put it under �qual
> to 
> 
> Andrew
> 
> 
0
11/10/2005 3:29:25 AM
thats better thanks


-- 
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639
View this thread: http://www.excelforum.com/showthread.php?threadid=483715

0
11/10/2005 5:17:04 AM
You're welcome.

koba wrote:
> thats better thanks
> 
> 
0
11/10/2005 5:57:28 AM
Reply:

Similar Artilces:

Vary Color based on value
Is there a way in a bar chart to have a bar be a specific color if less than, lets say "5" ? Instead of manually changing each bar that is less than a specific number. Hi, Create two additonal columns of formulas, suppose your original data is in B2:B12, then in C2 enter the formula =IF(B2<=5,B2,0) in cell D2 enter the formula =IF(B2>5,B2,0) Copy these formulas down. Plot these ranges instead of your original range. Format one series one color the other another. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Jennifer" wrote: >...

Merging rows
office 2003 excel How can I merge two rows into one row? Also... where is that in help? I spent about 45 minutes trying to find it. Thanks John if you want to merge A1:A10 and B1:B10, select C1:C10 and type in C1 =A1&B1 and ctrl+enter. Neo Cheung wrote: > if you want to merge A1:A10 and B1:B10, select C1:C10 and type in C1 > =A1&B1 and ctrl+enter. > Thanks... works great. Now, how do I get it to be just the data and not the reference to the other rows. thanks John Hi John, To eliminate the formula leaving only the value --- select column C, Ctrl+C (edit, copy), ...

How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 ...

Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have no use for all of the rows at the same time and have to keep hiding and showing them as need arises. Is there a macro to hide chunks of it based on the value of one cell of the row? In other words, en each row I will have a formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to hide the row or not. I tried case.select but it takes a LONG time and I would have to write a piece of code for every line. FYI, the rows that need hiding will be in sequence, in other words, fro...

Auto height rows
I am working in Excel 2000 for Windows with cells that are NOT merged. The individual cells have varying amounts of data (anywhere from a few characters to over 1000 characters). All text is wrapped. When I double click on the row to auto height, it adjusts the height but the printed version still cuts off text. In most cases the row height does not come anywhere near 409.5 pixels. I saw the problem with bottom aligned text, but all my cells are top aligned. I am working with 19 files that include at least four worksheets with 300+ rows of data. It's getting time consuming to re...

Why have I got some repeating rows after a Excel 2003 sort
I copied and sorted an existing worksheet which had been obtained from an unknown source. I found some of the rows were duplicated after the sort, but with the number column showing different results from the original row and not formatted. The original worksheet had no duplicates. Why would this happen? I have used older versions of Excel for years and this has never happened before. Hi jayne are you sure there were no hidden rows in the original data? Cheers JulieD "Jayne" <Jayne@discussions.microsoft.com> wrote in message news:D5C7F6BE-EE34-434A-B829-68B2FA6C4AD2@...

coloring cells
I want to alternate colors between successive rows. One blue one white, the next one blue and the next white and so on to the end of my worksheet. It there a easier way than to hold the control key and click on each row? Question # 2 when I sort will the colors stay with the row or will they move with the sort. Is there a way to get the colors to stay even when I sort? You can use conditional formatting which applies the colour using the Mod() function: http://www.ozgrid.com/Excel/alternate-row-color.htm After sorting, every second row remains the same colour as before sorting. ...

I need excel to only use the last 26 rows in a chart
I have a large spreadsheet that has data in it that is entered weekly. I would like for the chart to grab the most recent 26 weeks of data automatically. Does anyone know of a formula that will do this? Logan: If you haven't already, you may want to establish dynamic ranges for your data set. This will allow you to automatically update your chart(s) as new data is entered It will also allow you to plot the last number of days/weeks that you want. This post explains how to set up dynamic range names and plot user specified number of days. It should get you started on dynamic range ...

Need help for Outlook 2002 -can not write or print email in color
I am using Word as email editor and lost the ability to see my email and print /send in color need advice color changes do not "stay" ARe you using HTML as the message format? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, elliotsh asked: | I am using Word as email editor | and lost the ability to see my email and print /send in | color | need advice | color changes do not "sta...

Every Other Row
Is there a way to get Excel to calculate Standard Deviation on every other row (there are 2400 rows, so manually putting them in isn't really an option.) ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** One way to try .. Assume the source data is in A1:A2400 in Sheet1 In a new Sheet2 ----------------------- Put in A1: =OFFSET(Sheet1!$A$1,ROW(A1)*2-2,) Copy A1 down to A1200 This'll extract every other row from Sheet1's A1:A2400 (i.e. from Sheet1's A1, A3, A7, A9, ... A2399) Then just put...

Setting text color to black in inactive combo boxes
Hi, I have a dialog with several edits & combo boxes (and labels too). The data is shown for view only and i need it to be shown in black color (text). For the edits i use the read-only property and it's doing the job. With the combo's i have a problem, The text is displayed in gray no matter what... anyone? Thanks. yuval.gatenio@gmail.com wrote: > Hi, > > I have a dialog with several edits & combo boxes (and labels too). > > The data is shown for view only and i need it to be shown in black > color (text). > > For the edits i use the read-only...

Rows and Columns
I am using Excel XP Each worksheet has 65,536 rows and 256 columns according to specifications. Is there a way to change these number to a smaller amount? I know I can hide rows and columns, but wondering if I can not have them to start them. TIA Bill The short answer is NO. Just don't format more than you need. -- Don Guillett SalesAid Software donaldb@281.com "EZDOIT" <Please@smile.com> wrote in message news:dxupc.98556$Ik.7697285@attbi_s53... > I am using Excel XP > > Each worksheet has 65,536 rows and 256 columns according to specifications. > >...

adding rows #2
How do I add rows without changing cell location in the formula. For example: The data for the formula is always located in cell B4 and if I add a row on top of B4, I want the formula to remain with B4. Right now, if I add a row, the cell in the formula goes down one row. Hope for help If I understand, you're adding a row higher than B4. This shifts B4 down, but you want the "new" B4 to be the data for the formula. If this is the case you could try this: =formula(OFFSET(A1,3,1,1,1)) That is, as long as you don't add a new top row. ...

Need help formatting rows
Hello everyone, I am using excel 2007 I would like to know how to do the following if possible have column A add column B and put the total in column C and then put the amount of column C in column A of the next row. EX: 1+1=2 in row 1 then automatically have it put 2 in row 2 column A Thanks in advance if anyone can help. Bobby Cell C1: = +A1 + B1 Cell A2: = +C1 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "bobby02169" wrote: > Hello everyone, > I am using excel 2...

background color #3
I know how to enter a picture to use as a background for an Exce spreadsheet that only shows up on the screen and does not print. Ho do you do the same for a solid color? I want to have the entir spreadsheet show up as pale yellow (to remind me to put in yellow pape in my printer) but I do not want the yellow to print -- denha ----------------------------------------------------------------------- denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2408 View this thread: http://www.excelforum.com/showthread.php?threadid=38309 can't you use Paint (or ...

Can Excel sort rows by color?
I want to sort a spreadsheet by the color of the rows. Here's a website that will help you accomplish this: http://www.cpearson.com/excel/SortByColor.htm tj "Grant" wrote: > I want to sort a spreadsheet by the color of the rows. Grant, have a look here http://www.cpearson.com/excel/SortByColor.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Grant" <Grant@discussions.microsoft.com> wrote in mes...

format group of rows based on condition
I've seen answers to similar problems, but nothing as complex as this: I have the following set-up (simplified) Type City Store actual Portland TB bid Portland TB actual Memphis AU bid Memphis AU bid Memphis AU I need to format in VBA (since this is over 3000 rows deep), in the following manner: Insert a row in between a change in the cities column. Then surround the grouping by city with a bold outline. Color the row with the bid gray and color the rows in between the cities blue. The sheet has...

XP Theme Colors
As part of an enchanced print preview class, I want to draw the background using colors of the XP theme, if active. I notice in Office 2003, it draws the background using an appropriate color from the XP theme. When themes aren't enabled, it uses the COLOR_APPWORKSPACE system color, which is also what the default MFC print preview mechanism uses. Does anyone know if Office is using their own adjusted colors or are they taking colors directly from the theme? I tried enumerating some colors using the GetThemeSysColor and GetThemeColor functions, but I can't find any color matching th...

repeat rows at top
Once you've created your "standard" colum headings, how do you repeat them on every page --- Message posted from http://www.ExcelForum.com/ "al_rwli >" <<al_rwli.zrrq5@excelforum-nospam.com> wrote in message news:al_rwli.zrrq5@excelforum-nospam.com... > Once you've created your "standard" colum headings, how do you repeat > them on every page File > Page Setup > Sheet tab Print titles: Rows to repeat at top File>page setup>sheet and put the reference in rows to repeat at top -- Regards, Peo Sjoblom "al_rwli ...

Sum Function Without Including Hidden Rows?
I'm using Excel 2000 and have *hidden* several rows that include numeric data. I want to do a sum function on some of the columns intersecting those rows, but when I insert an AutoSum function, it includes data in the visible rows AND the hidden rows. Is there an easy way to perform a sum function that *only* includes the rows that are *-visible?-* (Which rows are hidden/visible are subject to constant change) Thanks for your help! --- Message posted from http://www.ExcelForum.com/ You would need a UDF for that or if there is a pattern (like if every other column/row is hidden) th...

highlight consecutive duplicate rows (re-sending)
My apologies if anyone responded to this when I originally posted. I didn't download enough headers and I can't recapture the original.... I want to highlight any row that has the same data as the previous row. For example, if A:3=A:2 and B:3=B:2 and C:3=C:2, I want to highlight Row 3. I also want to repeat this logic throughout the spreadsheet. Can I do with this with Conditional Formatting? Or, would I need to use VBA...in which case, how would I code this? Thanks in advance. I have no idea if anyone responded to your earlier post, but you could check yourself: You can ...

make row height a little bigger in a wrapped cell
I've got a sheet with columns at work date text (sometimes multi-line wrapped) $ nnnn.nn I can wrap text no problem but I hope approximate double spacing so I don't have to put a blank row between each item. I need the space between items so the text isn't squashed all together for a court form I guess I want to be able to say height of cell necessary for the wrapped text however much it might be plus n for each row height. I can't find a way. Is it possible? Excel 2002 Win xp Thanks for your help. If you're using merged cells, then this w...

format colors on bar charts by value
I'm working on a Bar chart with 5 bars. What I would like to do is change the color of each bar depending on the number it is graphing. My table is very simple, column A is the data names, column B are the values from 0 to 3. If the value is 1 I would like it to change the color of the bar to red, if it is 2 I'd like it to change to yellow, if it is 3 I'd like it to change to green. Can this be done? Thanks Bill See http://peltiertech.com/Excel/Charts/ConditionalChart1.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "BillC...

Advanced Sorting (4000 rows) followup Q.
Green John blue cats $50.00 Happy Green 2 John blue cats $50.00 Green 2 blue John cats $50.00 Green 2 blue cats John $50.00 Green 2 blue cats $50.00 John What I want: For example, I want "John" to be sorted to the first column for each row. Problem: In my original table, there are over 20+ Columns and 4,000 rows. Sorting by row, three at at time would take forever, especially since I have over 100 different databases that I need to do this on. Without sorting by rows three at a time, what's the best solution? If there's no way to do this in excel without a t...

Crosstab Row Heading Issues
Hi, I have Access 2003 and when I do a cross tab I cannot bring in more than 3 field values for row headings - my coworker (who has Access 97) can bring in >3 row headings. Is this a paremter setting in Access 2003? Thanks, Mike I believe that the Wizard can't handle more than 3; however, you can do more with SQL. Try creating a crosstab with the three headings, them modifying it at SQL level to add more. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "mike" wrote: > Hi, > > I have Access 2003 and...