I would like to enter text in a cell and it autofill cells in that row with black (to black out)
Ex: data I enter in E38 will automatically fill with black for cells J38,K38,L38,M38 etc.
I would like the rule to be applicable for all my rows but be independent for all my rows
22 Answers
You can achieve that by using Conditional Formatting.
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. Conditional Formatting is the leftmost thing in that section.
Click it and a new menu opens. Go almost to the bottom of it and click New Rule. A box for that opens up. In the top half of it are some kinds of rules Excel will implement for you without much effort on your part. The bottom one in that section is the one you want: Use a formula to determine which cells to format is its label. Click it.
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. You are going to enter a short formula in that. Start by clicking in it, and typing the =.
Your formula will do two things. One is it will test cell E38 for whether it has a value in it or does not. The second is it will be written in a way that makes it the test for only one row, as you ask for. More on that in a moment.
One formula you can use is NOT(ISBLANK($E38)) which directly tests whether there is something in it or not. 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. And since the new SPILL functionality puts something there, even if it looks blank, it won't work if that could happen. Another good one would be $E38="" which tests to see if it contains... nothing... I recommend this one. It works in a straightforward way, and the test it makes is not ruined by SPILL functions. There are other ideas too, but go with the second one. So what you type is exactly:
=$E38<>""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. NOT having one of those $'s before the 38 lets Excel make changes if you copy and paste, etc. 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. That's how you make it work only for each row. So if E405 has a value, the cells you want blacked out will only be the row 405 cells, not all the other rows.
There are a few other things to do next. You need to tell Excel the formatting choices you'd like to make. And you need to tell Excel the range of cells to apply the rule to.
Formatting choices first. After typing in the formula, notice that the bottom area also has a button labelled Format. Click it. 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. Go to the Fill tab and choose the color you want (Black). Good so far. 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. I'm guessing you don't want that... So go to the Font tab and set the font color to Black as well. Then it will override any normal change they make.
Good enough for now.
Click OK to accept the changes which returns you to the box you entered the formula in. Click OK there too to accept the whole bit of work. 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.
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. That column is your next interest.
If you know the range you want turned black, then type = and the range address. If you want the range J38:T38, just type =J38:T38 and click OK at the bottom. 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.
(You don't care what was there, if anything, before you do this. Just click in the box, highlight anything there, and start typing so that it all disappears and only your typing is there.
The Applies to range is where you make sure all the cells you want the rule to cover are listed. So if you want 3,000 rows starting with row 38, you'd enter =J38:T3037. Do NOT do anything like that in the formula you typed... it will have a very different effect. 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.
When you're happy with the range you typed, click OK to wrap things up and return to the normal stuff.
That's it, all done. You might like to test it, always a great idea, and figure out what went wrong if it doesn't act like you wish.
Do bear in mind that if your goal is to make these cells unreadable in the spreadsheet, there are many ways for users to find out what is in each cell. All involve some effort (work) on their parts, but they can EASILY do it.
If your goal was to make things unreadable on a report, this will work perfectly. No problema.
Select the Range you want to format, J38:M38 and go to Home- Conditional Formatting- New Rule- select Use a formula to determine which cells to format- enter the formula and fill the cell color to black:
=$E$38<>""
