View Single Post
 
Old 12-19-2016, 04:26 PM
deejay deejay is offline Windows 7 64bit Office 2016
Novice
 
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