Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

edit & print conditional format formulas

0

conditional formatting is not something i do with every worksheet, only if i want to visually see a set of data in a table without searching the entire table

for example i color code my inventory based on various criteria. example follows.

when you attempt to edit the formula, say add/change criteria(condition),  you cannot scroll thru the formula using arrow keys. doing so inserts a cell(s) referance info.

also when completed and you actually test the condition(s) it would be advantageous to get a printout,should anything get erased or modified--handy goto referance

=OR(IF($B2="Verified",1),IF($B2="Located",1),IF($B2="kit cover #",1),IF($B2="*part of KIT",1),IF($B2="xfer shelf bin",1))

specifically I am asking : when i edit formula in the conditioal format dialog, you cannot see the entire formula, so i want to know how to 'navigate' thru the formula to make changes ... arrow keys will not work, yes i can copy/edit in another cell/paste back ... but you will have to exit/reopen dialog...is there a way to navigate thru the formula other then using arrow keys.
(the 'code' i presented was to show how long the text string can be VS size of the dialog window to enter the string itself)

2nd question was how to printout all you conditioal format 'code' for keeping as a referance

Answer
Discuss

Discussion

What exactly is your question?
don (rep: 1989) Jul 7, '16 at 10:37 am
when i edit formula in the conditioal format dialog, you cannot see the entire formula, so i want to know how to 'navigate' thru the formula to make changes ... arrow keys will not work, yes i can copy/edit in another cell/paste back ... but you will have to exit/reopen dialog...is there a way to navigate thru the formula other then using arrow keys.
(the 'code' i presented was to show how long the text string can be VS size of the dialog window to enter the string itself)

2nd question was how to printout all you conditioal format 'code' for keeping as a referance
jhixy (rep: 2) Jul 7, '16 at 11:14 am
Ok I got it. I will get an answer out to you shortly. In the meantime, please update your original question with the new information that you provided so that someone else who has the same issue can better follow the question.
don (rep: 1989) Jul 7, '16 at 11:29 am
Thanks Don, I typically dont post anything (as you can see i dont get mypoint out very clearly) on forums, but i did update my original post as you asked...thanks for the advice
jhixy (rep: 2) Jul 7, '16 at 12:31 pm
no worries! 
don (rep: 1989) Jul 7, '16 at 1:54 pm
If this answer worked for you, don't forget to Select it as the Answer. If it didn't work, let me know what else you need.
don (rep: 1989) Jul 8, '16 at 11:51 am
Add to Discussion

Answers

0
Selected Answer

Basically, there isn't a "great" way to edit conditional formulas without running into the problem that you mention. You have to use the mouse cursor and click and drag to scroll over but that is very annoying.

In the end, I do what you already mentioned, which is to copy them to the worksheet, edit them there, make sure they work, then put them back into the conditional formatting window. I also create the formulas within the worksheet first so that I don't have to come back to them.

That said, your second question, printing out the formulas into the worksheet, is probably the best way to be able to quickly edit all of them.

I found this nice little macro online and made a couple small changes.

List all Conditional Formatting Rules from a Workbook:

Option Explicit
Sub List_Conditional_Formatting_Rules()

Dim ws As Worksheet, wsCF As Worksheet, NR As Long
Dim CFrule As FormatCondition, Rng As Range

On Error Resume Next

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "CF Rules"

Set wsCF = Sheets("CF Rules")
wsCF.Range("A1:C1").Value = [{"Sheet","Formula","Range"}]
NR = 2

For Each ws In Worksheets
 If ws.Name <> "CF Rules" Then
  Set Rng = ws.Cells
  For Each CFrule In Rng.FormatConditions
   wsCF.Range("A" & NR).Value = ws.Name
   wsCF.Range("B" & NR).Value = "'" & CFrule.Formula1
   wsCF.Range("C" & NR).Value = CFrule.AppliesTo.Address
   NR = NR + 1
  Next CFrule
 End If
Next ws

wsCF.Columns.AutoFit

Range("A2").Select

End Sub
Discuss

Discussion

i will try the macro for printing, but since we both have moreless concluded 'cut n paste' method, those 'formulas' will already exist somewhere in the xls sheet (other tab etc..), so recalling formatting text string (if altered) already is avaliable to be viewed vs saving a printout etc...

thanks again for your replies
jhixy (rep: 2) Jul 8, '16 at 12:19 pm
MACRO WORKED GREAT !!!
i thought id have to edit the macro to select cells, ranges, sheet, etc...to get it to run.
but i copied the macro as is and 'RUN'...result was outstanding !!
thank you again for helping to find that solution to the problem (still wish i didnt have to cut n paste edits to the format window...hey i cant be greedy)
jhixy (rep: 2) Jul 8, '16 at 12:31 pm
unfortunately i cant vote for the answer..(says i cant vote but keep participating and i will soon)
jhixy (rep: 2) Jul 8, '16 at 1:13 pm
I'm glad it worked so well! :) 
don (rep: 1989) Jul 8, '16 at 3:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login