Excel Formula for formatting tables

Asked

Viewed 72 times

0

Is there a formula for chaining the colors of a table? I don’t want "normal" (e.g. red|white|red). What I want is whenever the name of a given cell is equal, then the background is color A, when the name of the cell is different then changes to color B, when the name changes again then the color returns to color A and so on.

Table example.

Teste    X    B   A    T   -> cor A
Teste    f    s   f    g   -> cor A
ALPHA    f    s   f    g   -> cor B
ALPHA    g    g   h    r   -> cor B
ALPHA    f    s   f    g   -> cor B
Beta     G    A   f    g   -> cor A
.......

The value to be compared is from the first column by the value of the first column of the previous column.

It is possible?

excel fórmula

  • Yes. You can do this with code, but it will only have effect on your machine, although allowing your Excel to perform codes is dangerous.

  • How do I do it? You can give an example of the code?

  • Go to the Macros menu -> Save Macro -> Ok. Make various style changes, colors, fills, etc. Go back to the Macros menu -> View Macros and see the code that Excel created for you. Create a new spreadsheet,.

2 answers

2

Normally I use conditional formatting, you can select only the mobile and then click on conditional formatting tab, or select the entire document.

After clicking on Conditional Formatting choose Highlight Cell Rules ai just choose which are the conditions to automatically change colors according to the word, if it is greater than, less than, equal to, etc....

Simple as that

  • That way I could already do but I wanted to try more dynamic, IE, the formula will see the value of the previous line to see if it is equal or not, if it is equal the color remains if it is different changes. Table has values to compare sorted alphabetically.

0

Good afternoon. If you can still help, and if an extra column can be created where it will have the criterion of conditional formatting, you can use the formula below:

=SE(A2=A1;D1;-(D1))

I made the formula in column D. Put the reference to the column where this formula is.

The value of cell D1 must be different from 0 (put 1, for easy).

In conditional formatting, you can select to "Use a formula to determine which cells should be formatted" and use the formula below:

=$D1=-1

Again, the reference to column "D" is according to the column where the formula will be.

The value "-1" is why I put value 1 in cell D1.

To not vary the reference in relation to the column where the formula is, put "$" before the "D" ("$D").

And the interval must start on the same line as the formula (in this case, line 1). If not, you have to count the difference between the reference.

I hope I’ve helped.

Browser other questions tagged excel fórmula

You are not signed in. Login or sign up in order to post.