 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2008[XL07] Conditionally format fill color for entire row
Hi All, Is it possible to conditionally format the fill color for an entire row based on a cell reference? For instance, Columns 1,2,and 3 are text but column 4 is a number. I'd like to fill the whole row blue if the number in that row equals 100. Then I think I can use the painter to copy the formatting to the rest of the sheet. Any ideas would be great. Thanks, Trevor Firstly: I would not apply this to the WHOLE row as XL2007 has 16,384 columns Second: why do you speak of 'column 4' rather than 'column D'? Third: here is how to do what you asked Select A1:K10 Use Home | Conditional Formatting | New Rules | "Use a formula ....." In dialog the formula to use is =$D1>=100 (or =$D1=100, as preferred) and format as required Note the 1 refers to row 1, so if you are using another starting row, adjust as needed. The $ is essential for your purpose. Note you can now use Conditional Formatting | Manages Rules and in the new dialog you can specify the range to which the rule applies best wishes  SignatureBernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email > Hi All, > [quoted text clipped - 14 lines] > > Trevor Use a conditional format, Formula Is and tie it to your target cell. Once you've verified it works, click the format painter then the row. SignatureTips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips > Hi All, > [quoted text clipped - 14 lines] > > Trevor I have a related problem. In trying to change the color of the entire row based on the contents of one cell in that row, only the first column gets changed. The format indicates that it applies to $A$3:$D$22. What have I done wrong? Thanks > Use a conditional format, Formula Is and tie it to your target cell. Once > you've verified it works, click the format painter then the row. [quoted text clipped - 17 lines] > > > > Trevor How does the formula look under formula is ? SignatureRegards, Peo Sjoblom >I have a related problem. In trying to change the color of the entire row > based on the contents of one cell in that row, only the first column gets [quoted text clipped - 26 lines] >> > >> > Trevor The formula reads as =D3="VG" and it applies to =$A$3:$D$27 Only the "A" column gets formatted. Did I provide what you needed? Thanks. > How does the formula look under formula is ? > [quoted text clipped - 28 lines] > >> > > >> > Trevor You need to understand the difference between relative and absolute addressing. Having seen what you've got in your formula for formatting A3, have a look at what you've got as the formula for cell B4, for example. If you are trying to apply the format to all columns as a result of the content of column D, then in your original formula change your =D3="VG" to =$D3="VG" -- David Biddulph > The formula reads as =D3="VG" and it applies to =$A$3:$D$27 > [quoted text clipped - 41 lines] >> >> > >> >> > Trevor Another way is to select all of the cells to be formatted first, then pick Conditional Format from the menu. > Another way is to select all of the cells to be formatted first, then > pick Conditional Format from the menu. Sorry, didn't finish my own post. I mean, select them all first, because then Excel should do the absolute/relative reference properly automatically. I tried this method, and it formatted the entire selection without regard for the conditional part. Obviously, I may have erred in the way I did it. I do have the problem handled via the post from David. Thanks for your input. > > Another way is to select all of the cells to be formatted first, then > > pick Conditional Format from the menu. [quoted text clipped - 3 lines] > I mean, select them all first, because then Excel should do the > absolute/relative reference properly automatically. Oops, I should have seen that myself. My age must be having an effect. Thanks for your help! > You need to understand the difference between relative and absolute > addressing. [quoted text clipped - 52 lines] > >> >> > > >> >> > Trevor Trevor: You would NOT want to fill the entire row, unless you really do have ALL those columns containing data.... Instead, name the range you want to fill and setup vb code on the Page change event to check for 100 in the field, if yes, fill it... if range("A1:A1") = 100 then With MyNamedRange .Interior.Color = RGB(0, 0, 255) .Font.Color = RGB(0, 0, 255) End With End If Assuming you have multiple lines like this setup a for next loop to inspect each range and cell. > Hi All, > [quoted text clipped - 14 lines] > > Trevor |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.