CODEWITHSUNDEEP
excelloops
mhyvarinen
mhyvarinen

Reputation: 15

Excel 2013 color fill when number changes in a column

I'm working on data-analysis where i would like to be able to automatize color fill when looking through large amount of data where there are abundant amount of ghost logs and taking too much of my time as they are severely irrelevant.

So what i would like to do in Excel, is to be able to color fill a cell when the number changes in a column marking a different set of logs.

For instance if there are six rows of log number 456455, i would like the code to color fill the first cell when the number changes to 456456 so that it helps me identify logs faster when i know where the sets are starting. I'm kind of a newbie when it comes to Excel but this loop would help me a lot!

Thx for your time

Upvotes: 0

Views: 65

Answers (2)

Crapsy
Crapsy

Reputation: 356

If you have some knowledge in VBA, you can implement a macro that looks at the column where you have your log number and if the value changes from one cell to another, then you highlight this cell. I attached a template of code that works for this task.

Sub highlightChange()
Dim preVal As Integer
preVal = 0

For Each o In Range("A:A")
    'Go through column
   If o.Value <> preVal Then
        o.Interior.Color = vbRed 'Color the selection
   End If
   preVal = o.Value
Next o
End Sub

There may be other solution without VBA, however, it is quite easy and practical to use a macro.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

This can be done with conditional formatting. Use a rule that compares the current cell with the cell in the row above and format if the two are different. Note that you will need to use relative references without $ signs. In the screenshot below, the conditional format is applied from row 2 to 19 and in row 2 the formula compares A2 with A1, in the next row it compares A3 with A2, and so on. If the two cells are different, the cell will change colour.

enter image description here

Upvotes: 4

Related Questions