Formatting cells based on specific formula using VBA

[Cipri]

Hello,
I have some knowledge of VBA, but quit basics, so I need help with some coding.
I have a file in which I need to do formatting based on a specific formula.
Basically, I need to put a specific color, on each cell of some columns, based on the value of the cell in the next column.
If the value on cell B2 is less then 0, but the value on A2 is higher then 0, then is yellow, if value from both A2 and B2 are higher then 0 is green, if the value on both are less then 0 then is red. Cell values from column B are always less or equal to the cell values corresponding from column A.
I need to run this down for around 1500 rows, and lateral for around 60 columns.

Can somebody helps me with a code that repeats this formatting process both down through the rows, and lateral through the columns?

One additional issue is that this formatting is not starting from the top of the page, but from a position somewhere in the page(currently at cell U39).

 

[6StringJazzer]

Any reason you don't want to use conditional formatting? It could be done with three rules.

 

[6StringJazzer]

First you're talking about A and B then you mention it starts at U39, so not sure what you really need.

The following rules will format all odd-numbered columns starting with A assuming your data continues in pairs of columns as shown. Because your conditions are mutually exclusive and collectively exhaustive, you can start by just formatting all the cells red. That will be the default.

Yellow:

=AND(ISODD(COLUMN(A1)),B1<0,A1>0)

Green:

=AND(ISODD(COLUMN(A1)),B1>0,A1>0)

You can start the rule in any odd-numbered column. If you start the rule at U then you must change A to U and B to V.

Since the rule applies to the entire column it doesn't matter how many rows of data you have.

 

[Cipri]

Any reason you don't want to use conditional formatting? It could be done with three rules.

Hi Jeff,
Currently I'm using conditional formatting, but this has led to a heavy file, which is very slow in response, hence I've thought that using a VB will make it quicker and more easy to use.
As a info, in the example I've provided, I've just created a dummy sample with the issue I'm facing for reference (hence the A & B columns) , while my real file has the area I need formatting starting with column U and row 36 and goes up to column DM and row 1500.
Regarding your guidance to use ISODD for formatting, I've tried it, but , maybe due my lack of experience, I haven't been able to make it work. Also, one thing which I don't know how to embed in the VBA code is to make this repeat in row relation.
Basically, in my file, the conditional formatting is relating something like this:
A2 is conditioned by B2, A3 is conditioned by B3, A4 is conditioned by B4, a.s.o. or to put it more in real U36 is depending on the values like below:
If U36 < 0 Then U36 needs to be Red
If U36 >0 and V36 < 0 Then U36 needs to be yellow
If U36 >0 and V36 >0 Then U36 needs to be green

And the above relations needs to be repeated for each row until 1500...

If U37 < 0 Then U37 needs to be Red
If U37 >0 and V37 < 0 Then U37 needs to be yellow
If U37 >0 and V37 >0 Then U37 needs to be green

So, a rule applying to the column, I'm not sure is going to work for what I'm needing.

Hope it's more clear now..

 

[6StringJazzer]

Been away all week.

I would still use CF.

And the above relations needs to be repeated for each row until 1500...

If U37 < 0 Then U37 needs to be Red
If U37 >0 and V37 < 0 Then U37 needs to be yellow
If U37 >0 and V37 >0 Then U37 needs to be green

No, it isn't repeated. You use ONE conditional formatting rule that applies to all rows.


These simple CF rules should not impact performance. The times that CF rules can impact performance is when array formulas are used for CF rules, which can require massive calculation over large ranges. I would like to see the CR rules you used that caused slow response. I suspect the issue is in how you wrote the rules, not in CF itself.

However, if you absolutely require VBA I (or someone) might be able to come up with something.

 

[Cipri]

Hello,

Please see below the CF rules I've used. The Red & Green have caused no issue, while the yellow has increased my file from 2-3 M up to 19 M ....

I would prefer to use the CF , and no VBA, because I need to share the result file with other people, and macro enabled files going over emails, it is a nightmare, but I can't use a 19 M file which is slow in response(take 2-5 minute for recalculating after each value I'm changing)... hence I was looking for VBA ...

 

[6StringJazzer]

You showed rules for "Show formatting rules for Current Selection", and these rules apply to just one cell.

What does it show when you select "Show formatting rules for This Worksheet"?

 

[Cipri]

Hello,

I've tried to show entire worksheet, but my computer has froze...See below the CF for a selection of a few cells only.

 

[6StringJazzer]

The problem is that you are duplicating the same rule for multiple cells. For example, in the shot you are showing here you should have one rule

Cell Value < 0

with an Applies To range of =$V$53:$V$54. That is only what is visible in this shot. In reality this range should probably be much bigger.

The same thing applies to the other two conditions. Just one rule for each condition, applied to all the relevant cells.