Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA to return the details of condtional formatting

Posted on 2014-09-22
1
Medium Priority
?
149 Views
Last Modified: 2014-09-23
I have two questions,

is it possible to have a vba code that lists all of the condtional formatting range and its condtional formula in a workbook?

secondly, how to set condtional formatting using vba.

for example in SELECTION OF range A1:A20  i want to set the formula =COUNTIFS($B$1:$B$20,$A1) in the condtional formatting to highlight it in yellow if the condtion is met.

appreciate your help.
0
Comment
Question by:ProfessorJimJam
1 Comment
 
LVL 43

Accepted Solution

by:
Steve Knight earned 2000 total points
ID: 40338440
Sure, something like this should do it... if it has to work with existing conditional formatting entries or not you might not need/want to clear existing conditions etc.

Steve

 
With Range("A1:A20")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIFS($B$1:$B$20,$A1)"
        .FormatConditions(1).SetFirstPriority
        .FormatConditions(1).Interior.Color = 65535
        .FormatConditions(1).StopIfTrue = False
    End With

Open in new window


For Each objFC In Range("A1:A20").FormatConditions
       Debug.Print objFC.Type, objFC.Interior.Color, objFC.Formula1
Next

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

581 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