Solved

Conditonal Formatting caaling modules

Posted on 2013-11-30
5
113 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok...I found the ISF as a range, Andrew.
0
 

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

10 Experts available now in Live!

Get 1:1 Help Now