Solved

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

Posted on 2011-09-21
11
395 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 80

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
 
LVL 80

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now