• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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

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
BobArnett
Asked:
BobArnett
  • 5
  • 4
  • 2
1 Solution
 
jan24Commented:
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
 
BobArnettAuthor Commented:
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
 
byundtCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
byundtCommented:
jan24's code is working for me in Excel 2010 with Conditional Formatting in cells A2:A10
CFwithUDF-Q27321019.xlsm
0
 
jan24Commented:
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
 
BobArnettAuthor Commented:
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
 
BobArnettAuthor Commented:
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
 
jan24Commented:
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
 
BobArnettAuthor Commented:
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
 
jan24Commented:
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
 
BobArnettAuthor Commented:
Thanks for the extra tips.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now