Solved

Conditonal Formatting caaling modules

Posted on 2013-11-30
5
124 Views
Last Modified: 2013-12-01
Folks,
Open the attached file and select the tab that is "UserDefineDetectCells"and colored red.
What you will see is a bordered range from A2:C10 with no visible data.
Click on "Display Data" command button and data appears.
The purpose for this is to identify only those cells that have formulas and there are two: C3 and C7 using conditonal formatting.
I use two macros. The first is:
Function HF(rng) As Boolean
HF = rng.HasFormula
End Function

Open in new window

I will use HF in my other macro called "DetectCellsFormulas" that uses conditional formatting. The formula there is =HF(A2)
MY problem is that cells with formulas are not being formatted?
CondFormatMacro.xlsm
0
Comment
Question by:Frank Freese
  • 3
5 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 250 total points
ID: 39687713
Well, you and I aren't the only ones having this problem. The method works manually but not when encoded. Here is an identical one - unsolved.
I recommend this method instead and have encoded the idea for you in the attached workbook. Note that both, the named range and all previously existing conditional formats are deleted when the code starts up.

Observe that the FormatConditions object's Formula1 property must include the leading "=" sign. You had omitted that, but adding it didn't make your code work any better.
EXX-131130-CF-Cells-With-Formula.xlsm
0
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 250 total points
ID: 39688151
Dear fh_freese,

See enclosed is my answer.  By the way, the statement of  Faustulus  is not true at all.  I used since 1994.  It is possible coding for conditional formatting, but it cannot apply to user defined function.

Please notes.

Andrew Man from Hong Kong
CondFormatMacro.xlsm
0
 

Author Comment

by:Frank Freese
ID: 39688342
Andrew,
Question: I've not been able to locate the formula = ISF that you used in his conditional formatting formula.
Both solutions are workable, so I'm grateful there.
Points to the two of you will be distributed equally. Since I have two workable solutions I beleieve that to be fair. Thank you
0
 

Author Comment

by:Frank Freese
ID: 39688596
Ok...I found the ISF as a range, Andrew.
0
 

Author Closing Comment

by:Frank Freese
ID: 39688599
thanks to all. I am grateful for EE's continued support.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

914 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

17 Experts available now in Live!

Get 1:1 Help Now