#1
|
|||
|
|||
Conditional Formatting problem
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 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 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 |
#2
|
|||
|
|||
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,"""")" =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. |
#3
|
|||
|
|||
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 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 |