CODEWITHSUNDEEP
excelconditional-formatting
cosgb
cosgb

Reputation: 1

Is there a way to to highlight rows with same number in 1st column?

is there a way to highlight same numbers on the 1st column? Or with 2 colors like in the picture?

I am looking for a way in google but cant find it. The picture below is an example and I did it manually for each row. I have 30k rows and I am wondering how to do it.

enter image description here

Upvotes: 0

Views: 307

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2639

You can use conditional formatting. In this case a formula-based conditional formatting. Since your rows needs to be either yellow or blank, you can also come up with a formula that returns either true or false. You can try your formula in your sheet before using it in the actual conditional formatting. Assuming that your first column is in column A, this formula might be of use:

=IF(ISEVEN(SUM(IF($A$1:$A1<>$A$2:$A2,1,0))),TRUE,FALSE)

We can chop the formula (thanks to this site) into this:

=IF(                                     'This if will return either TRUE or FALSE
    ISEVEN(                              'by checking whether we obtain an even number
        SUM(                             'from the count
            IF(                          'of of any previous cells that differs from their pre-
                $A$1:$A1 <> $A$2:$A2,    '-vious one. 
                1,
                0
            )
        )
    ),
    TRUE,
    FALSE
)

Note how the references to the ranges $A$1:$A1<>$A$2:$A2 are set. Each A is made absolute, while only half of the row references are absolute. This way each cell will compute the column A from its second row to the actual cell in the column A at the same row of the given cell (and to its previous one).

Now that you have the formula, you can apply a conditional formatting. Copy the formula (not the cell that contains it, the formula itself). Select your whole list starting from cell A2 (the starting cell is important). In the Home menu select Conditional Formatting and then pick New rule. In the window that will appear, select the formula based option and paste your formula in the proper textbox. Then click on the Format button and set your format (a solid yellow filled cell). Accept it all.

Upvotes: 1

Related Questions