Solved

Conditional formatting of a cell depending on "Hidden" status of cell above.

Posted on 2011-09-21
11
414 Views
Last Modified: 2013-12-26
I'm looking for a way to format a cell with, say, a bold top line if the cell immediately above is hidden. I can find no Excel function that reveals whether a target cell is hidden or not. Am I missing something obvious or is this just not available? I was hoping for something like the "CELL(info_type, [reference])" function but I cannot find what I need i.e. "=cell("hidden",A1)"
0
Comment
Question by:BobArnett
  • 5
  • 4
  • 2
11 Comments
 
LVL 2

Accepted Solution

by:
jan24 earned 500 total points
ID: 36577307
Go to the VBA editor and add a module, then pop this code in it:
Function ISHIDDEN(r As Range)
  ISHIDDEN = (r.Height = 0) Or (r.Width = 0)
End Function

Open in new window

You can then use the ISHIDDEN() function in your conditional formatting formula condition.
0
 

Author Comment

by:BobArnett
ID: 36577525
This works in a cell formula but I cannot get it to work in a conditional formatting formula. I keep getting the message that I cannot reference other worksheets or workbooks in Conditional Formatting. I merely put =ISHIDDEN($a1) as the formula applying it to the range "$A$2:$A$10". If I put the formula in double quotes ' ="ISHIDDEN($A1)"  ' then I don't get the error but it just doesn't change the formatting when I hide one of the rows.
0
 
LVL 81

Expert Comment

by:byundt
ID: 36577531
jan24 showed one way to determine if a row or column was hidden.

If instead you want to know whether the formula is hidden (on a protected worksheet), then you need a different user-defined function:
Function IsHidden(cel As Range) As Boolean
IsHidden = cel.FormulaHidden
End Function

Open in new window


You may see the Hidden property of a range in the VBA Help, but that does not work the way you might expect. It applies instead to the entire row or column:
Function IsHidden(cel As Range) As Boolean
IsHidden = cel.EntireRow.Hidden Or cel.EntireColumn.Hidden
End Function

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 81

Expert Comment

by:byundt
ID: 36577559
jan24's code is working for me in Excel 2010 with Conditional Formatting in cells A2:A10
CFwithUDF-Q27321019.xlsm
0
 
LVL 2

Expert Comment

by:jan24
ID: 36577566
That's odd - I just tried it and it worked for me (Excel 2007).  Have a look at the attached any see if you can figure out how it is different from yours.
Book8.xlsm
0
 

Author Closing Comment

by:BobArnett
ID: 36583787
Got it! Dummy me. It didn't dawn on me that saving my file as "ISHIDDEN.XLSX" would confuse the program. Excel saw that in the formula and treated it as a file. Once I changed the name of the file it worked as you all said it does. Thanks for the help.
0
 

Author Comment

by:BobArnett
ID: 36583980
Well, that was weird, after accepting the solution now I can't duplicate my (your) success. I'm back to it not working. I've uploaded the file to see if you can see where my problem lies.
testhide.xlsm
0
 
LVL 2

Expert Comment

by:jan24
ID: 36584082
Hi Bob,
You'd done it right - it was just that for some reason the VBA wasn't there.  That meant that ISHIDDEN() returned a #NAME? error because it didn't recognise the name ISHIDDEN.
Attached is a version where I've added the VBA code back in.  Be sure to enable macros when you open the file, if it asks you.

Fingers crossed! testhide2.xlsm
0
 

Author Comment

by:BobArnett
ID: 36584267
The corrected xlsm works fine. One thing I may be doing wrong is that I tried to put the VBA code in my PERSONAL.XLSB that opens when I start Excel. This is how I've added other FUNCTIONS to be available but it seems like if I put it there then it won't work properly. I do note that when I open Excel and view the code there are several VBAProjects (with xlam extensions) open that duplicate some of the FUNCTIONS I have in the PERSONAL.XLSB. I hope I'm not asking too much but could give me a short rundown on the difference in these files.
0
 
LVL 2

Expert Comment

by:jan24
ID: 36585653
Each of the VBAProjects you see corresponds to either an open workbook, or an add-in.  Basically if you put the code in the workbook then it is only available when that workbook is open.  The benefit of the code being in the workbook is that when you send the workbook to someone else then they will have the macro too.

On the other hand, the benefit of putting it in PERSONAL.XLSB is that it is available to you all the time, albeit not to anyone else.  If you are having problems with PERSONAL.XLSB, then try these links:
http://www.lytebyte.com/2008/09/09/how-to-unhide-personalxlsb-and-edit-it-in-excel-2007/
http://www.eggheadcafe.com/microsoft/Excel/30445352/personalxlsb-does-not-load-automatically-when-i-start-excel-2007.aspx
0
 

Author Comment

by:BobArnett
ID: 36587725
Thanks for the extra tips.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question