General discussion

Locked

Excel 2007 - Conditional Formatting

By plante.gabriel ·
Hi everybody,

I have a question in Excel 2007 about the conditional formatting.

I have a table and this table has 1 column per day for a given month. (from 1 to 31 for the month of March)

There is 40 rows.

What I want to do is change the formatting of a cell if it's value is bigger than the cell on it's left (the day before) and I want to do that for every cell, for every day.

I also want to do a different formatting if the value is smaller.

I tried with conditional formatting but I think I'll need VBA to achieve this, I tried but I'm not very good in it, any of you could give me some help please?

Regards,

Gabriel

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

INDIRECT, ADDRESS, ROW, COLUMN functions

by Aaron Mason In reply to Excel 2007 - Conditional ...

Conditional formatting is a good place to make use of the INDIRECT, ADDRESS, ROW and COLUMN functions.

To refer to the same column in the row above, you can say ADDRESS(row()-1, column(), 4, true) - this will return a string saying, in the case of row B3, B2. Slap this into INDIRECT() and this will pull a value from the cell above it.

You can then use this to refer to the cell above dynamically. You can put the same conditions onto each and every cell at the same time and it will cover them all.

Hope this helps

Collapse -

You are my hero.

by plante.gabriel In reply to INDIRECT, ADDRESS, ROW, C ...

Thanks a lot man, it worked.

I had to modify it a little bit, here what I did:

In a new conditional formatting rule, I put this forumula:

=((INDIRECT(ADDRESS(ROW(), COLUMN()-1, 4))) < (INDIRECT(ADDRESS(ROW(), COLUMN(), 4))))

It checks the cell on the left of the current one and then see if it's lower, if it is then I put it green.

I did the reverse thing for red and it works perfectly.

thanks again man.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Forums