#1
|
|||
|
|||
Conditional formating all cells in an array based on adjacent cells
MS Excel 2016
I have fifty cells in a horizontal array (row) and I want each cell conditionally formatted based on the value in the cell above it. (fill for D3 is white if value for D2 =0), (fill for E3 is white if value for E2 =0), etc I have tried setting the rule to apply to all fifty cells, with the criteria using the cell above the first cell in the array. That worked in Excel 2010. but not here. I tried setting Format and Conditional Formating on the first cell and using Format Painter as suggested in Help text and that copied as rule criteria for all cells the criteria from the first cell (the one above it) without slewing the reference as Copying normally does. I tried setting Format and Conditional Formating on the first cell and using (right click - drag) Fill Formatting Only and that, like the method above, copied as rule criteria for all cells the criteria from the first cell (the one above it) without slewing the reference as Filling normally does. I don't want to individually create a Rule for one cell FIFTY TIMES and believe there must be a better way. Thanks for a definitive solution (other than the brute force one). :-) |
#2
|
||||
|
||||
Select the range to format ( D3:AA3,say)
Open CF wizard New rule enter =D$2=0 Format as needed Done ( the $ sign before the row number is important)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
no bueno
Nope, that didn't work either. I understand fixing the row and/or column value in a cell reference, and thought (if that had worked) that it would be odd to make fixing the row value force the column value to slew through the application of that rule. But, it didn't do that anyway. After applying that rule exactly as described, each cell *shows* D2 (the criteria cell for the origin cell in the array) as its criteria cell - but even with that the conditional formatting does not work on ANY of the cells in the array.
FWIW, this is not the first time that expert advice that works in one copy of MS Excel 2016 simply doesn't work in another. I am more convinced than ever that Excel (and to a lesser degree the other major Office apps) has just been totally screwed up by the decline in dev discipline and attitude at MS over the past 10-12 years. As much as I think the G suite Spreadsheets is inferior in its set of features, I will probably have to shift to that since what they do offer actually works. But thanks for trying. |
#4
|
||||
|
||||
And opening this, what happens?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
That works.
I noticed that the Manage Rules dialog box shows the formula in your wb to be exactly: Formula:=D$2=0 whereas in mine, after I entered it just that way, showed Formula:="D$2=0" When I removed the double quotes from mine it worked. Excel seems to have inserted specifically what broke the Conditional Formatting. I seem to recall something like this in Excel several versions of Office back. Has that fault been "re-implemented" in Excel 2016 ? Any chance MS will ... fix it ? Thanks very much. Last edited by deejay; 12-20-2016 at 10:05 AM. Reason: forgot social graces |
#6
|
||||
|
||||
No
that is probably because you entered D$2=0 without the preceding = sign XL then adds the = sign and thinks your expression is a text string, I don't know why. CF has many rules not very well documented
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
You may be right. Thanks.
Since you are so good, here is a question I've had for some time that I just ignored and worked around by turning off formula error detection (which is generally terrible, I think). What in the world is the legitimate cause and meaning of this error message, "The formula in this cell refers to a range that has additional numbers adjacent to it." That could naturally occur in a BUNCH of places where it is certainly not an error. AND YET, it shows up inconsistently. Where I filled the same formula - =IF(B41="","",COUNTA(D41:BA41)) - down a column (50 rows), and the contents of the referenced array are identical on each row, the error message shows up in only the last 13 cells in that vertical array. Je vois que vous êtes en Belgique. J'y suis allé plusieurs fois et j'ai 4 Belges dans ma vie ici à Austin, au Texas. Joyeux Noel. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding adjacent cells and having the sum appear in two separate places | Seamus Sean | Excel | 4 | 12-11-2016 10:34 PM |
Using alternating borders in adjacent cells | jrevard | Word | 0 | 10-15-2015 09:07 AM |
How to conditionally format cells in Col. A if it matches adjacent cell in Col. B? | alshcover | Excel | 2 | 06-03-2014 12:50 PM |
How can I count cells based on their fill color that was set by conditional format | trueimage | Excel Programming | 1 | 10-25-2013 12:08 PM |
Conditional Formatting Expiration Dates Based on Text in Adjacent Cell | Frogggg | Excel | 1 | 10-25-2011 08:44 PM |