Highlight Rows

Is there a way to program an expression close to... If cell in column E 
equals string of text "Pending", select entire row and change background 
color/patern to yellow?
0
Utf
4/6/2010 9:33:01 PM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
756 Views

Similar Articles

[PageSpeed] 2

Hi,

Conditional formatting. Select the row(s) to which this applies then

Format|Conditional format - Select 'Formula is' from the dropdown and enter 
the formula

=$E2="Pending"

Click Format and on the 'Pattern' tab and choose yellow. OK

Note that you should change the 2 to the top row of your selection
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Napoleon Vang" wrote:

> Is there a way to program an expression close to... If cell in column E 
> equals string of text "Pending", select entire row and change background 
> color/patern to yellow?
0
Utf
4/6/2010 9:41:02 PM
Sub colorif()
Dim c As Range
For Each c In Range("e1:e21")
If c = "pending" Then c.EntireRow.Interior.ColorIndex = 6
Next c
End Sub
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Napoleon Vang" <Napoleon Vang@discussions.microsoft.com> wrote in message 
news:630F0EEE-2924-4BCA-BA05-CA998F962995@microsoft.com...
> Is there a way to program an expression close to... If cell in column E
> equals string of text "Pending", select entire row and change background
> color/patern to yellow? 

0
Don
4/6/2010 9:44:35 PM
Helps a great ton!
Though, is there a way to add, at the end of that also
"else, make the row in question the default style"?

I find that after deleting the word "Pending" it remains yellow

"Mike H" wrote:

> Hi,
> 
> Conditional formatting. Select the row(s) to which this applies then
> 
> Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> the formula
> 
> =$E2="Pending"
> 
> Click Format and on the 'Pattern' tab and choose yellow. OK
> 
> Note that you should change the 2 to the top row of your selection
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Napoleon Vang" wrote:
> 
> > Is there a way to program an expression close to... If cell in column E 
> > equals string of text "Pending", select entire row and change background 
> > color/patern to yellow?
0
Utf
4/6/2010 9:52:07 PM
> I find that after deleting the word "Pending" it remains yellow


I'm more than a little surprised it does that. Try tapping F9, does the 
yellow clear?

If it does then calculation is probably set to manual

Tools|Options|Calculation tab and select Automatic
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Napoleon Vang" wrote:

> Helps a great ton!
> Though, is there a way to add, at the end of that also
> "else, make the row in question the default style"?
> 
> I find that after deleting the word "Pending" it remains yellow
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Conditional formatting. Select the row(s) to which this applies then
> > 
> > Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> > the formula
> > 
> > =$E2="Pending"
> > 
> > Click Format and on the 'Pattern' tab and choose yellow. OK
> > 
> > Note that you should change the 2 to the top row of your selection
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Napoleon Vang" wrote:
> > 
> > > Is there a way to program an expression close to... If cell in column E 
> > > equals string of text "Pending", select entire row and change background 
> > > color/patern to yellow?
0
Utf
4/6/2010 9:59:01 PM
And also another problem I just discovered.. I am currently using 

Tools > CF > =MOD(ROW(),2)=1

to alternate the row colors. and it's not allowing me to highlights the 
alternate rows..


"Mike H" wrote:

> Hi,
> 
> Conditional formatting. Select the row(s) to which this applies then
> 
> Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> the formula
> 
> =$E2="Pending"
> 
> Click Format and on the 'Pattern' tab and choose yellow. OK
> 
> Note that you should change the 2 to the top row of your selection
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Napoleon Vang" wrote:
> 
> > Is there a way to program an expression close to... If cell in column E 
> > equals string of text "Pending", select entire row and change background 
> > color/patern to yellow?
0
Utf
4/6/2010 10:00:06 PM
Hi,

These 2 conditional formats should not conflict provided

=$E1="Pending"

Is the first CF condition. You then click ADD and enter your second condition.

See my other post about calculation
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Napoleon Vang" wrote:

> And also another problem I just discovered.. I am currently using 
> 
> Tools > CF > =MOD(ROW(),2)=1
> 
> to alternate the row colors. and it's not allowing me to highlights the 
> alternate rows..
> 
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Conditional formatting. Select the row(s) to which this applies then
> > 
> > Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> > the formula
> > 
> > =$E2="Pending"
> > 
> > Click Format and on the 'Pattern' tab and choose yellow. OK
> > 
> > Note that you should change the 2 to the top row of your selection
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Napoleon Vang" wrote:
> > 
> > > Is there a way to program an expression close to... If cell in column E 
> > > equals string of text "Pending", select entire row and change background 
> > > color/patern to yellow?
0
Utf
4/6/2010 10:08:01 PM
Mike.. You are the.. MAN!!! WAHOOOO

"Mike H" wrote:

> Hi,
> 
> These 2 conditional formats should not conflict provided
> 
> =$E1="Pending"
> 
> Is the first CF condition. You then click ADD and enter your second condition.
> 
> See my other post about calculation
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Napoleon Vang" wrote:
> 
> > And also another problem I just discovered.. I am currently using 
> > 
> > Tools > CF > =MOD(ROW(),2)=1
> > 
> > to alternate the row colors. and it's not allowing me to highlights the 
> > alternate rows..
> > 
> > 
> > "Mike H" wrote:
> > 
> > > Hi,
> > > 
> > > Conditional formatting. Select the row(s) to which this applies then
> > > 
> > > Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> > > the formula
> > > 
> > > =$E2="Pending"
> > > 
> > > Click Format and on the 'Pattern' tab and choose yellow. OK
> > > 
> > > Note that you should change the 2 to the top row of your selection
> > > -- 
> > > Mike
> > > 
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > > introduces the fewest assumptions while still sufficiently answering the 
> > > question.
> > > 
> > > 
> > > "Napoleon Vang" wrote:
> > > 
> > > > Is there a way to program an expression close to... If cell in column E 
> > > > equals string of text "Pending", select entire row and change background 
> > > > color/patern to yellow?
0
Utf
4/6/2010 10:42:01 PM
You seem to be happy, glad I could help
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Napoleon Vang" wrote:

> Mike.. You are the.. MAN!!! WAHOOOO
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > These 2 conditional formats should not conflict provided
> > 
> > =$E1="Pending"
> > 
> > Is the first CF condition. You then click ADD and enter your second condition.
> > 
> > See my other post about calculation
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Napoleon Vang" wrote:
> > 
> > > And also another problem I just discovered.. I am currently using 
> > > 
> > > Tools > CF > =MOD(ROW(),2)=1
> > > 
> > > to alternate the row colors. and it's not allowing me to highlights the 
> > > alternate rows..
> > > 
> > > 
> > > "Mike H" wrote:
> > > 
> > > > Hi,
> > > > 
> > > > Conditional formatting. Select the row(s) to which this applies then
> > > > 
> > > > Format|Conditional format - Select 'Formula is' from the dropdown and enter 
> > > > the formula
> > > > 
> > > > =$E2="Pending"
> > > > 
> > > > Click Format and on the 'Pattern' tab and choose yellow. OK
> > > > 
> > > > Note that you should change the 2 to the top row of your selection
> > > > -- 
> > > > Mike
> > > > 
> > > > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > > > introduces the fewest assumptions while still sufficiently answering the 
> > > > question.
> > > > 
> > > > 
> > > > "Napoleon Vang" wrote:
> > > > 
> > > > > Is there a way to program an expression close to... If cell in column E 
> > > > > equals string of text "Pending", select entire row and change background 
> > > > > color/patern to yellow?
0
Utf
4/6/2010 11:35:01 PM
Reply:

Similar Artilces:

Rows are hidden and wont come back
I have this spreadsheet and the row #'s go in order from #1 thru 9131 and then the next row # is 11,725 Question: Why is this happening? Question: HOW did this happen? Question: HOW do I make all of the rows between 9132 & 11725 display? Thanks! Hi The row heigth is set to 0! Select the range 9131:11725, and set the row heigth to >0 -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message news:000F1D28-CDC9-4E45-B420-FD50025B0030@microsoft.com... > I have this spreadsheet...

I want to delete rows with duplicate entries within one column.
I want to remove a complete row which contains duplicate entries in a specific column kini olegario" <kini olegario@discussions.microsoft.com> wrote in message >I want to remove a complete row which contains duplicate entries in a > specific column Hi Kini. See Chip Pearson's DEleting Rows web page at: http://www.cpearson.com/excel/deleting.htm --- Regards, Norman " ...

Column and Row spacing?
The code below shows: ColA Qty1 Qty2 Qty3 can I modify it to show ColA -ColB-ColC Qty1 ------Qty2 -----------Qty3 Code: 'Sheets("nvT").Range("c" & Columns.Count).End(xlUp) (2).Resize(, 1).Value = qty The code below shows: ColA-ColB-ColC Qty1-Qty2-Qty3 can I modify it to show: ColA -ColB-ColC Qty1 ------Qty2 -----------Qty3 Code: 'Worksheets("nvT").Range("IV7", Worksheets("nvT").Cells(7, Columns.Count).End(xlToLeft))(3).Value = qty Hi, You can do this with a spreadsheet formula: In cell B2 enter: =IF(ROW()=C...

Click 1 Excel square, computer highlights 3 vertical squares, why
Using Windows 7, I haven't had any problems until now. When I click on 1 square, the computer highlights 3 verical squares. Did I turn something "on" and not know it? And how do I get it to highlight only the square that I click on? hi i'm assuming that you are talking about excel 07...... some have reported this as a bug and adjusting the zoom usually helps. see this previous post. http://tinyurl.com/3wofsf if you are using 03 or earlier, try tapping the f8 key regards FSt1 "Calbobi" wrote: > Using Windows 7, I haven't had any pro...

automatic highlighting of row and column cell intersects
I am trying to find out if it is possible to have Excel automatically highlight(lightly shade) the row and column of the active cell I am in. That is if I am in cell H13, then the whole row of 13 is highlighted and the complete column of H is highlighted . That way I can quickly see all the information in row 13 quickly at a glance (i.e. information in cell B13). The highlighting would move accordingly to which ever active cell I am in. So as I move down then the subsequent row would highlight. James Try Chip's site: http://www.cpearson.com/excel/rowliner.htm Andy. "James"...

Auto Highlight largest value in column
Hi Again, How would I format a column to highlight the cell that has the highest value? Thanks again. Hi - select your range (e.g. A1:A10) - goto format - Conditional Format - in this dialog enter the formula =A1=MAX($A$1:$A$10) - choose your format -- Regards Frank Kabel Frankfurt, Germany Brian wrote: > Hi Again, > > How would I format a column to highlight the cell that > has the highest value? > > Thanks again. Frank, Thanks for the speedy reply, but it doesn't seem to work. Can I format the entire column instead of a selected range? Thanks. >-----Or...

Converting Rows of Data to Columns
I have the following data, for example. Location # Vendor # 151 250368 151 312956 151 485648 103 251142 103 255442 My list has many, many locations. Anyone know an easy way to convert this list so there is one line per location, with columns B-D (for example) representing the vendor number. Here's an example of what I'm looking for. Location B C D 151 250368 312956 485648 103 2...

Decrease size of a worksheet so it has fewer than 64,000 rows?
I do not need for the worksheet to be so big. How do I decrease the size, not just delete the data from the rows. sophy A worksheet has 65536 rows and 256 columns, no less and no more. You can hide the rows and columns you don't use, but they will still be there. Gord Dibben Excel MVP On Wed, 28 Sep 2005 14:14:01 -0700, sophy2 <sophy2@discussions.microsoft.com> wrote: >I do not need for the worksheet to be so big. How do I decrease the size, not >just delete the data from the rows. If you or someone accidentally entered information in the last row or so you could ...

Highlighting Cell Shitfs Focus
I highlight a cell in 2007 and start to type in a formula and all of a sudden I'm typing in a completely different cell. This has happened at least 100 times. It never happened in 2003. Very frustrating. ...

ROW TO COLUMNS
I CAN VIEW TEXT FROM A FORM ON MY WEBSITE IN EXCEL, HOWEVER, THAT DATA FROM THE FORM IS IN THE FIRST COLUMN. (A1-A20) i NEED TO VIEW THE DATA ACROSS THE TOP OF MY WORKSHEET IN THE COLUMS. I NEED THE DATA VIEWED IN A2 TO BE VIEWED IN B2, A3 IN C1, A4 IN D..etc. i WILL BE ABLE TO USE THE AUTO FILTER FEATURE THEN. hOW CAN I DO THIS? Highlight your column of data and Copy. Place the cell pointer in A1 and select Paste Special and tick the Transpose box in the options box that opens. Click OK. Your data is now in Row 1 columns A-?? HTH Chet A couple of suggestions if you're going ...

how do I edit gridlines in one row
I want to create chart heading. When doing this, I need to erese the column sperators in the first two rows, only. I tried it, in "format cells," but to no avail. Is there some way to do this or do I need to create my accounting chart, then edit it in Powerpoint? Hi unless you've selected otherwise gridlines don't print (file / page setup / sheet tab) ... however you CAN merge the cells - select them and choose format / cells / alignment tab - merge cells but a better option (merged cells can cause you problems if you ever want to sort or filter) would be to sele...

How do I highlight all lookup formulas in a worksheet in one shot.
I need to implement cell protection to all cells with lookup formula in a worksheet. I know GOTO-Special can highlight all the formula cells, but I need some way more specific to pick and highlight only the lookup formula. Try this macro: Option Explicit Sub FindLookup() Dim FormulaRng As Range Dim FormulaCell As Range Dim sFormula As String On Error GoTo NoFormulas Set FormulaRng = ActiveSheet.Cells. _ SpecialCells(xlCellTypeFormulas) On Error GoTo 0 For Each FormulaCell In FormulaRng With FormulaCell sFormula = LCase(.Formula) If InStr(sFormula, "lookup")...

Unable to group rows or columns on an excel worksheet
I'm unable to group/ungroup rows or columns on an excel worksheet that was emailed to me by a co-worker. It's an existing file so I don't know what restrictions were placed on the workbook. What can I do to fix this problem? If the sheet is protected, unprotect it. You may need a password. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Christine - HCVT" <ChristineHCVT@discussions.microsoft.com> wrote in message news:C0BDA314-BE7...

Hiding rows based on a value
I am looking for a way to hide a row based on the value in a cell. EG: a price list which hides rows where the quantity column is '0' so that when you print it / view it on screen you only see relevant rows. Thanks. Menu Data>Filter>Autofilter and select a value of 0. -- HTH RP (remove nothere from the email address if mailing direct) "John" <John@discussions.microsoft.com> wrote in message news:132DD4F9-06F2-4434-97DB-78E5CC4D3505@microsoft.com... > I am looking for a way to hide a row based on the value in a cell. > > EG: a price list which hi...

Sum of all numbers before a blank row
I have an excel file which i have sorted by column 'Ordered Item'. Next to this column is a 'Price' column. The 'Ordered Item' Column may have duplicates. What I need to do is put a subtotal beside each group of 'Ordered Items' (even if they are single items). How would I sum the Prices every time there is a change in the data in column 'Ordered Items'? Ordered Items Price 241 50 241 50 340 25 600 100 600 100 So if the sheet looks like this, how ...

How do I get Excel to count identical rows in a list?
I have a large list of data and I need some way to get Excel to count the number of identical rows. The list is sorted so that identical rows are adjacent to each other. I need excel to put a number in another column stating how many exact replicates of that row are present in the worksheet. If possible I would also like excel to delete the replicates and leave only one row with a count of the number of replicates it has deleted. I am using Excel 2002 on Windows XP Professional. Hi how many columns are filled with data?. you may use a formula such as =SUMPRODUCT(--($A$1$A$100=A1),--($...

Delete row based on contents of cell
The following code does a great job of finding whole blank rows and deleting them: Range("B1", Range("B65536").End(xlUp)).SpecialCells _ (xlCellTypeBlanks).EntireRow.Delete However, could someone please tell me how to modify this for two other purposes: 1) Delete the whole row if any cell in column A: is has the contents of "SSN" 2) Delete the whole row if any cell in column C: is blank (null?) Thank you in advance. One way is to use data>filter>autofilter and record while doing it to learn. Post back after you have tried this with addl questions. ...

Add rows to table
This is a template - I want to be able to add rows to a table (7 colums). Below is example of table: Each <> is a form field The Net1 is a formula of GAmt1 - (GAmt1 * Rate1) By clicking the macroButton field <insert row above> a new row would be added above and the form fields to be named 'Pol2' , 'Name2', etc. Net2 would be formula same as for Net1. Next added row would have 'Pol3', Name3', etc. The field after the text 'Total' be a formula of adding the Net Amounts (Net1 + Net2 + .....) for as many rows that are added. Also would...

How to change font style for highlighting using ITextDocument
Hello, Using the Rich Edit Control I am able to change the font style at will for text ranges but this causes the document to be flagged as modified which is not what I want, how can I modify the font but not effect the modified flat? Eli >Using the Rich Edit Control I am able to change the font style at will for >text ranges but this causes the document to be flagged as modified which is >not what I want, how can I modify the font but not effect the modified flat? Eli, Have you considered calling CRichEditCtrl::SetModify to clear the control's modified flag? Dave -- MVP VC...

ROW function does not work as expected
Hello, I am trying to conditionally format a cell based on two values: the value of another cell and the value of this cell itself. I am using the following: =IF(AND(INDIRECT(ADDRESS(ROW();3))="B";D6>0);TRUE;FALSE) Check if in the current row at column 3 the value is "B" and if the value of this cell is greater than 0 This does work if I write it directly in a cell. If I use the exact same formula in the conditional formatting dialog, nothing happens. I learned that in Excel 2003 there was a bug concerning the INDIRECT and AND function (http://groups.google.de/group/m...

Trying to have this code generate only a numerical value list by rows: 1, 2, 3, ... 10, 11
Originally this was used to create an alpha listing. I use this to create hiearchy and need the code to count upwards from 1 by 1 as high as is needed. (probably at least to three digits). Code being used: lastletter = "1" Cells(2, 16) = lastletter For intalpha = 3 To Cells(65536, 3).End(xlUp).Row If Cells(intalpha, 2) = 0 Then lastletter = Chr(Asc(lastletter) + 1) Cells(intalpha, 16) = lastletter Next intalpha ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software!http://www.ozgrid.com/Services/excel-software-categories.htm ** Your co...

need to make cell blank if no data in that row
I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

Print titles must be contiguous and complete rows or columns
Excel 2003 - Printing titles - I'm Getting "Print titles must be contiguous and complete rows or columns" when I select more then one rows to repeat print on everypage. What am I doing wrong. Hi SG- Make sure to click the Row Number of the row containing the content to reprint. If more than one row, they must be consecutive (1,2,3 not just 1 & 3). It may also help to drag or Shift+Click to select multiple rows -- make sure to _not_ use the Ctrl key. If this still does not work, it may be that the row you are selecting contains merged cells from more than one row. HT...

W2003: find cursor location in table coordinates (table/row/column
I've been asked to extract data from an existing form that I didn't set up, and the format is less than ideal. To minimize my cycles of testing, it would be much easier to place my cursor in a sample document, and then debug.print the cursor location as table coordinates so I can hardcode the 'cells' that I need to extract. There are about 15 tables total, and I'll have to extract content from most of them. I played around with the range.parent object but couldn't figure out how to return the table info. I also googled, but no joy. Any suggestions...

How do you delete filtered rows in an autofiltered list. The row.
When I auto filter a spreadsheet, I can't figure out how to delete or eliminate from the sheet the filtered-out rows. So when I refilter the sheet with new items to get rid of, the old filtered items come back. I've tried copying the range to another file, but I always get all the old data in the new file. Select the rows you want to delete and click edit, delete. "The Dunce" wrote: > When I auto filter a spreadsheet, I can't figure out how to delete or > eliminate from the sheet the filtered-out rows. So when I refilter the sheet > with new items to ...