PDA

View Full Version : Solved: Use Conditional Formatting?



YellowLabPro
09-10-2006, 03:22 AM
I am attempting to fill a range w/ color if proven true. I am having trouble understanding how to write this line.

The following line returns end of statment error on the . separator prior to interior color:

ws2.Range("C2:C").Formula = "=If(C2>900)".Interior.Color = vbblack


The macro recorded this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
Selection.FormatConditions(1).Interior.ColorIndex = 1
Selection.Copy
Range("A3:D20").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


My first step is to get this to work in one cell, then the row, then the range.


thanks,

YLP

Bob Phillips
09-10-2006, 03:43 AM
With Range("A3:D20")
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With

YellowLabPro
09-10-2006, 03:54 AM
Thanks Xld,
Could you elaborate on a couple of points?
1) What is wrong w/ my formula, (trying to grasp adding formulas in my code w/ correct syntax)
2) What is going on w/ your formula syntax? You have Formula1:=
In other examples I have seen, .Formula = is used
3) And lastly, why do you have to use With-End With construct?

thanks,

YLP

Bob Phillips
09-10-2006, 08:34 AM
Your formula is not a formula.

- the range is unfinished
- you are trying to combine the formula with the conditional action
- conditional formatting can have two formulae, that is wht you use Formula1, Formula is reserved to set a range formula

and I use With .. End With to save repeating the range qualifier.

YellowLabPro
09-10-2006, 08:49 AM
XLD,
A couple of more things I need help on. The code is actually not catching the right range.
I will upload the file.
I also have been working w/ changing the code not to be hardcoded to look at the last row, but I am having trouble w/ this too.

My line of code is:
With ws2.Range("A2:D" & LRow2).Rows(Rows.Count, 1).End(xlUp).Row
where it is hanging up.

The full section is:

With ws2.Range("A2:D" & LRow2).Rows(Rows.Count, 1).End(xlUp).Row
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With

Bob Phillips
09-10-2006, 08:57 AM
You have a strange habit of trying to run separate statements into one



With ws2
LRow2 = .Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A2:D" & LRow2)
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With
End With

Norie
09-10-2006, 09:00 AM
Yelp

Why do you have this?

ws2.Range("A2:D" & LRow2).Rows(Rows.Count, 1).End(xlUp).Row
That won't return a range, I think it will only return a number.

YellowLabPro
09-10-2006, 09:14 AM
I was referencing this as my line of code that I am attempting to use instead of the hardcoded example that XLD is using. The range will change. It will begin in A2 and could extend anywhere into D.
But currently, the code does not actually fill the right row as we have the code written. It only picks up one row, where there are several meeting the criteria, and that row is incorrect.



Why do you have this?

ws2.Range("A2:D" & LRow2).Rows(Rows.Count, 1).End(xlUp).Row
That won't return a range, I think it will only return a number.

Thanks...

YLP aka "Yelp" :hi:

Norie
09-10-2006, 09:22 AM
Yelp (Doug?)

The problem with the code is that using code to apply conditional formatting sometimes creates weird results.

Now I'm afraid I've no idea why that is and I don't really know of real solution.

The only way I've been able to combat it is to select the range before setting the conditional formatting, or run the code again.

This slight alteration to xld's code worked first time for me.


With ws2.Range("A2:D" & LRow2)
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With

YellowLabPro
09-10-2006, 09:39 AM
Hmmm,
I will try this, thank you.

Yes, YLP / Doug,
I post under YLP for VBA-- and Doug for Excel. This helps keep me things straight for me... :bug:

YellowLabPro
09-10-2006, 09:56 AM
Norie,
Thanks the last bit of code did the trick.

Thank you

ylp

Norie
09-10-2006, 10:13 AM
Yelp

No problem.:)

Mind you, like I said, I don't know why this behaviour happens.

I might try and find out, I'll post back if I find anything.

I don't know if this is the exact solution but when you use R1C1 notation the conditional formatting seems to be correct first time, without having to select.

With ws2.Range("A2:D" & LRow2)
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC3>=1998"
.FormatConditions(1).Interior.ColorIndex = 1
End With

YellowLabPro
09-10-2006, 10:32 AM
Xld,
It is hilarious that you stated I have a strange habit....
I am still soooo clueless and confused most of the time that whatever habits I have are unintentional...

thanks for your help, that is one thing I am sure of...:clap:

YLP

Bob Phillips
09-10-2006, 12:18 PM
Mind you, like I said, I don't know why this behaviour happens.

I might try and find out, I'll post back if I find anything.

It is strange behaviour, it is exactly as CF was designed to work. Because Excel adjusts the formula relative to the active cell (which is an incredibly useful feature), it is necessary to either adjust the formula yourself in relation to the activecell, or avtivate the first cell in the range to be formatted. My paper on conditional formatting conditions covers this, but from a perspective of counting cells in a range that are formatted.

Norie
09-10-2006, 12:22 PM
xld

I think I understand now that you've mentioned the active cell thing, sort of makes sense.

I wonder why using R1C1 seems to solve the problem?

I've only tested it with simple formulas like Yelp's but that approach seems to work.

Can you post a link to your paper?

YellowLabPro
09-10-2006, 12:23 PM
My paper on conditional formatting conditions covers this, but from a perspective of counting cells in a range that are formatted.
Can you provide the link to this paper?

Bob Phillips
09-10-2006, 01:08 PM
It ain't easy stuff

http://www.xldynamic.com/source/xld.CFConditions.html (http://www.xldynamic.com/source/xld.CFConditions.html)

Norie
09-10-2006, 01:27 PM
Bob

Good article, and I think I've actually seen it before.:)

But I'm still curious of why using R1C1 notation appears to work.

Mind you I've just realised I've not actually tested that with the active cell thing.

And I just have, it still seems to work with the R1C1 notation no matter what cell is active.

Sorry if I'm confusing anybody but I'm a little bit (lots, really) confused myself.