Solved

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

Posted on 2011-09-21
11
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Suggested Courses

626 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