Excel will keep a watch on those 3,000 column E cells and if one of them changes from blank to having any entry, it will change that ROW's formatting, but won't change any other row's formatting. Do NOT do anything like that in the formula you typed.
So if you want 3,000 rows starting with row 38, you'd enter =J38:T3037. The Applies to range is where you make sure all the cells you want the rule to cover are listed. Just click in the box, highlight anything there, and start typing so that it all disappears and only your typing is there. (You don't care what was there, if anything, before you do this. You CAN use the mouse to select the range you want if you like, and while I don't do that much, many do and wouldn't have it any other way. If you want the range J38:T38, just type =J38:T38 and click OK at the bottom. If you know the range you want turned black, then type = and the range address. The first column should have your formula, the second a depiction of what the formatting looks like (a solid black bar in this case!), and the third column should have the range it Applies to. You will then be taken to a box called the Conditional Formatting Rules Manager and in the main part of it you should see one rule. Click OK there too to accept the whole bit of work. Then it will override any normal change they make.Ĭlick OK to accept the changes which returns you to the box you entered the formula in. So go to the Font tab and set the font color to Black as well. However, a person could use normal formatting to change the font color to, say, white, and the cells would be as readable as before you did anything. Go to the Fill tab and choose the color you want (Black). A normal formatting box opens up, and although it has less things in it than normal, you use it exactly the same way as normal formatting for a cell. After typing in the formula, notice that the bottom area also has a button labelled Format. And you need to tell Excel the range of cells to apply the rule to.įormatting choices first. You need to tell Excel the formatting choices you'd like to make. So if E405 has a value, the cells you want blacked out will only be the row 405 cells, not all the other rows. That's how you make it work only for each row. The $E makes sure it keeps looking at column E and the 38 without a $ lets it look at any row's column E cell instead of staying locked in on row 38's. NOT having one of those $'s before the 38 lets Excel make changes if you copy and paste, etc. That $ before the E tells Excel that no matter how you copy and paste the cell, etc., it should still keep referring to column E. There are other ideas too, but go with the second one. It works in a straightforward way, and the test it makes is not ruined by SPILL functions. Another good one would be $E38="" which tests to see if it contains. And since the new SPILL functionality puts something there, even if it looks blank, it won't work if that could happen. But it tests for one thing, then gives Excel the opposite of the result, which is fine, except it's harder to understand when looking at it. One formula you can use is NOT(ISBLANK($E38)) which directly tests whether there is something in it or not. The second is it will be written in a way that makes it the test for only one row, as you ask for. One is it will test cell E38 for whether it has a value in it or does not. Start by clicking in it, and typing the =. You are going to enter a short formula in that. The bottom half of the box now changes and you will see an area just below what you just clicked that you can type in. The bottom one in that section is the one you want: Use a formula to determine which cells to format is its label. In the top half of it are some kinds of rules Excel will implement for you without much effort on your part. Go almost to the bottom of it and click New Rule. Conditional Formatting is the leftmost thing in that section.Ĭlick it and a new menu opens. You get to where you need to be with it by clicking the Home tab of the Ribbon, then looking right of center for the Styles section. You can achieve that by using Conditional Formatting.
0 kommentar(er)
