Conditional Formatting problem

EC37 · #1 07-09-2014, 08:16 AM

Hello again!

I'm working on another export and trying to get Conditional Formatting to work again- but seems to be getting hung up on the actual font formatting piece. More specifically:

Code:

org.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

At least that's where it errors out and has me 'Debug'- which leaves that line highlighted.

The section itself pertaining to the Conditional Formatting is:

Code:

cc = Range("A2").End(xlDown).Row
Set org = Range("B" & cc)
    org.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(AND($I3>$S$1,$I3<$U$1),$I3,"""")"
    org.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
     With org.FormatConditions(1).Font
            .Strikethrough = False
            .Color = -16727809
            .TintAndShade = 0
     End With
org.FormatConditions(1).StopIfTrue = False

Also hoping someone can check how I have 'org' set up. I want to format all cells in column B (less B1, the header row). However, since not all cells are populated in column B (therefore End(xlDown) would only select up to a certain point) I have it referencing column A- since this column will always have contents. The program doesn't yell at the way I have it set up now- so I'm guessing it's OK! But just wanted to double check.

I have another export template that i have, where the conditional formatting is written the EXACT same way- so I don't understand why it is causing a problem here.

Any help is appreciated (still new to VBA)

Thanks
E

whatsup · #2 07-09-2014, 09:35 AM

Hi

Now, I didn't have a very close look to your code, but one thing isn't correct:

Code:

Formula1:="=IF(AND($I3>$S$1,$I3<$U$1),$I3,"""")"

I gave you yesterday the formula, and maybe it looked odd to you that it will work. But it will:
=AND($I3>$S$1,$I3<$U$1)

The thing is, excel expects a result either True or False. On just this result it's decided if the format gets applied or not.
The formula you use returns a result, the else-part returns text (empty string). In case your result is numeric, every number > 0 is interpreted as True. A emtpy string maybe will bring up some confusion.

Another thing, you will have to check:
Inserting a formula when references aren't absolut is a tricky thing. Your reference $I3 might change the reference of row 3 to another row, in case a cell is selected not within row 3.

EC37 · #3 07-14-2014, 01:02 PM

Sorry for the late response- but thanks. I had been googling what formula to use initially- before I saw your response. But makes sense to take the "IF" out of the equasion.

Anywho- I realized some mistakes with my code as well and fixed. Everything works properly now. One of the main problems was I had copied the format and didn't change some things. For instance:

Code:

org.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

If you look at my original post, I never Selected anything, therefore using "Selection" makes no sense. So Selection was replaced with what I should have been referencing: '(org.FormatConditions.Count)'.

There were a couple of other minor tweaks made before it started to work properly. But appreciate the response!
I only come on here during work, and had some crap I had to attend to Thurs, and don't work on Friday's.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional formatting that ignores other formatting rules	info_guy2	Excel	1	07-03-2014 10:07 AM
Conditional Formatting	jdc15371	Excel	2	06-26-2014 06:34 AM
Problem with Conditional Formatting	wrighty50	Excel	11	03-25-2012 10:42 AM
Conditional formatting	zanat0s	Excel	4	01-20-2012 03:30 AM
Conditional Formatting Problem Excel 2007	namedujour	Excel	6	08-04-2011 10:52 AM