bobrossi56
asked on
Filling a color in a row based on a cell text value in that row, Excel 2010
I have a spreadsheet that Martin Liss helped me with some time ago and it works great. It basically does number calculation via code instead of formulas. The attached spreadsheet is an example of it. The format of this sheet never changes, the # of rows will change, but the columns are always going to look like this.
Unfortunately the spreadsheet is hard to read and the rows blend into each other. I want to be able to fill in each row with a subtle yellow anytime a cell in column D says REVENUE. I manually did the first 3 rows of this attached example, but I have 7 of these spreadsheets to do each month and many of them have hundreds of rows, so manually is not an option. What can I use on each of these 7 spreadsheets that will fill yellow in the revenue rows?
thx experts...
BobR
Goodrich-file.xlsm
Unfortunately the spreadsheet is hard to read and the rows blend into each other. I want to be able to fill in each row with a subtle yellow anytime a cell in column D says REVENUE. I manually did the first 3 rows of this attached example, but I have 7 of these spreadsheets to do each month and many of them have hundreds of rows, so manually is not an option. What can I use on each of these 7 spreadsheets that will fill yellow in the revenue rows?
thx experts...
BobR
Goodrich-file.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I missed something in my formula =$B4="REVENU"
ASKER
OK, Martin, your instructions only colored the cells in column D, I need the entire row colored when D = revenue.
BClongacre - I tried your instructions and nothing got colored.
MacroShadow - your example was perfect, but you did not list the steps you took to accomplish it so I am still in the dark.
BClongacre - I tried your instructions and nothing got colored.
MacroShadow - your example was perfect, but you did not list the steps you took to accomplish it so I am still in the dark.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK bclongacre....Let me attach 2 screen shots top this showing what I have done, which yields me no fill anywhere....I believe I asked for any cell in column D that had the word REVENUE in it, to fill yellow from D3 to Q3 for as many rows as the sheet had, I picked 200 rows for this example.
thx
2016-05-12_16-03-43.jpg
2016-05-12_16-02-20.jpg
thx
2016-05-12_16-03-43.jpg
2016-05-12_16-02-20.jpg
Okay, the issue here is that, based off of the rule that you have selected, you need the final box to only state Revenue
If you were using the manually enter formula option, then you would use a formula where you define the specific cell $D4 (filled down, would then scale to the specific rows)
If you were using the manually enter formula option, then you would use a formula where you define the specific cell $D4 (filled down, would then scale to the specific rows)
Also, it does appear that you have selected Conditional formatting which will ONLY affect the specific Cells that have the value of Revenue.
You may want to rebuild the Conditional formatting
I have attached 3 screen shots demonstrating what I would recommend to resolve this issue.
Screen_shot_1_-_Conditionial_Formatt.png
Screen_shot_2_-_Conditionial_Formatt.png
Screen_shot_3_-_Conditionial_Formatt.png
You may want to rebuild the Conditional formatting
I have attached 3 screen shots demonstrating what I would recommend to resolve this issue.
Screen_shot_1_-_Conditionial_Formatt.png
Screen_shot_2_-_Conditionial_Formatt.png
Screen_shot_3_-_Conditionial_Formatt.png
ASKER
I don't really feel my problem was understood as some solutions didn't even accomplish what I was looking for, or did not include the steps on how they got their example to work. After poking around and asking some co-workers I was able to accomplish what I wanted. Thanks to all that replied.
Can you show a picture of what you wanted?
1. Select the cells you want affected by the conditional formatting (in you sample it is $E$3:$Q$46)
2. Click Home tab >> Conditional Formatting >> New Rule
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =$D3="Revenue"
5. Click Format.
6. In the Color box, select your desired color.
7. Click OK until the dialog boxes are closed.
2. Click Home tab >> Conditional Formatting >> New Rule
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =$D3="Revenue"
5. Click Format.
6. In the Color box, select your desired color.
7. Click OK until the dialog boxes are closed.
ASKER
I included an attached file showing what I wanted Martin. Seems like only 1 expert actually opened it. Hence my comments.
Well there were then at least two of us since I also opened it, but the problem was that the sheet was not scrolled to the top and the yellow rows were not visible. So when I loaded the workbook I didn't see the yellow rows, but no big deal.
ASKER
Ah ha, caught by the old scroll snafu. Been there, done that.
In this case you would highlight the section/row that you want to have the formatting applied to, on the Home tab, Click Conditional Formatting, New Rule, Select Use a formula to determine which cells to format.
In the formula field enter =$%columnletterNumber e.g. =$B4
Click the Format button, and select the desired formatting.
Click OK
Then Click Conditional Formatting, Manage Rules, and change the range to be you want to effect. e.g. Starting Range $A$4:$H$4, revised Range to cover say 10 Rows $A$4:$H$14