Microsoft Office Forums Conditional formating all cells in an array based on adjacent cells

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-19-2016, 04:26 PM
deejay deejay is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2016
Novice
Conditional formating all cells in an array based on adjacent cells
 
Join Date: Dec 2016
Posts: 4
deejay is on a distinguished road
Post 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). :-)
Reply With Quote
  #2  
Old 12-20-2016, 12:36 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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)
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #3  
Old 12-20-2016, 09:32 AM
deejay deejay is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2016
Novice
Conditional formating all cells in an array based on adjacent cells
 
Join Date: Dec 2016
Posts: 4
deejay is on a distinguished road
Unhappy 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.
Reply With Quote
  #4  
Old 12-20-2016, 09:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

And opening this, what happens?
Attached Files
File Type: xlsx CFTest.xlsx (9.0 KB, 3 views)
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #5  
Old 12-20-2016, 10:03 AM
deejay deejay is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2016
Novice
Conditional formating all cells in an array based on adjacent cells
 
Join Date: Dec 2016
Posts: 4
deejay is on a distinguished road
Smile

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
Reply With Quote
  #6  
Old 12-20-2016, 10:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #7  
Old 12-20-2016, 12:00 PM
deejay deejay is offline Conditional formating all cells in an array based on adjacent cells Windows 7 64bit Conditional formating all cells in an array based on adjacent cells Office 2016
Novice
Conditional formating all cells in an array based on adjacent cells
 
Join Date: Dec 2016
Posts: 4
deejay is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formating all cells in an array based on adjacent cells 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
Conditional formating all cells in an array based on adjacent cells 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
Conditional formating all cells in an array based on adjacent cells 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


All times are GMT -7. The time now is 12:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft