CODEWITHSUNDEEP
excelvbascriptingformatting
thoughtMaze
thoughtMaze

Reputation: 35

Shading cells when row value changes, across multiple rows AND columns EXCEL

I need to be able to

-compare row data for each column(from cell B2; to the end, ie. whole worksheet minus first row and column)

-if no data changes in said column; then highlight the column heading

-if data changes; highlight the first cell where the data is changing

please see example, keep in mind the excel sheets I have are much much larger, like huge, so using reference columns wont work, and multiple formulas for each column wont work either. I found this for single column and its great, =INDIRECT("A"&ROW())<>INDIRECT("A"&(ROW()-1)), but I can't apply this to over 100 columns, not practical. Please help.

enter image description here enter image description here

Bahh, I wrote one myself, its not dynamic on the number of rows or columns yet, but it does the job, and I think I don't have to go through both rows and columns, but I couldn't figure out how to reference the cell positions otherwise fast enough. Better then RobB solution, flapping his gums. 

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
Dim flag As Boolean
Columns().Font.Color = vbBlack
Rows().Interior.ColorIndex = 0
flag = False
For j = 2 To 120 'Must hard code number of columns
    For i = 3 To 3300 'Must hard code numbe of rows
        If Cells(i, j).Value <> Cells(i - 1, j) And Not IsEmpty(Cells(i, j).Value) Then
            Cells(i, j).Interior.ColorIndex = 37
            flag = True
        Else
            If flag Then
                Cells(1, j).Interior.ColorIndex = 36
            End If
        End If

    Next i
    flag = False
Next j
End Sub

Upvotes: 0

Views: 166

Answers (1)

RobB
RobB

Reputation: 29

This sounds like a simple task for a macro - though this would require you to do something manually (eg press a key) to initiate the comparison.

Upvotes: -1

Related Questions