VBA Conditional Formatting Formula1

[scottc_00]

I have a range of cells that I want to compare to the cells immediately to the left (meaning I want to compare cell C5 to cell B5). If C5 is less than B5 I want it to turn red. I want to do this for a range of cells. C5:C20 and E5:E20 and G5:G20 etc. I have named this range MyRange. I originally thought I could use the OFFSET function but I get an error.

Please Login or Register  to view this content.

When I run this code it fails at the .Add line. I assume the problem is with the syntax of Range(MyRange).Offset(0,-1) but I don’t know. Anyone have any idea how to use VBA to set the conditional formatting using a formula by comparing the cell value to the cell to the left? A few notes… The reason I want to use VBA is that the user may select different formulas and the highlighting will change based on the selection (The users can select Percentage or Count or Variance). The vba is kicked off by an Event Procedure (Not sure this makes any difference but thought Id mention it)
Thanks

[Teblol]

Cant you just use conditional formatting? Use:

Please Login or Register  to view this content.

This will check if C1 is less than B1. If it is then you can format the cell to be red.

[scottc_00]

I cant reply. I am getting blocked by a firewall. It says I can not use HTML. I am not trying to. Anyone have any idea how I can reply to this post ?

[scottc_00]

Anyway that doesnt work ... Cant tell you why because the firewall wont let me.

[Teblol]

It appears as though you were successful with replying?

[scottc_00]

Nope. it let me say that much but it wont let me type much. I have reached out to the Site Admins for asst. meanwhile I will try a little at a time. This is painful!

[scottc_00]

First it failed on C1 “Variable not defined”.

[scottc_00]

So I tried “C1” and got a Type Mismatch error.

[scottc_00]

then I tried putting the word Range in front of the Cell References like this ... Range("C1")

[scottc_00]

which ran, but all the cells were highlighted whether they were less than or not. AND when I checked the actual Conditional formatting on the cells the formula was Cell Value < FALSE. SO maybe I am misunderstanding your advise or I haven’t found the right combination. Because the range is complex I would love to be able to use offset or some form of it like Row(o)Column(-1) or R0C-1 or something.

[Teblol]

Take a look at my example. If you select column C and choose conditional formatting > manage rules you will be able to see how it should be set up and work.

Conditional formatting for scottc_00.xlsx

EDIT: If that doesn't help, are you able to attach your workbook?

[scottc_00]

Thanks Teblol for your help. Your solution

Please Login or Register  to view this content.

works but I dont really want to spin thru each cell individually. After looking into it some more, I fear I will have to consider each cell individually. However, with a little tweaking I was able to at lease not have to expressly tell it where to stop and start beyond identifying the range.

Please Login or Register  to view this content.

Thanks again for your help.