CODEWITHSUNDEEP
excelvba
user1624926
user1624926

Reputation: 451

How to highlight contents in an Array

I have setup an Array so I can check the content of a worksheet. Where I find inconsistences I want to highlight them. I "tried" to use .Interior.colourindex but this doesn't work. I researched it and this function changes the interior colour of the cell, as I'm in an Array I can't do this. Below is my code with the .interior.colourindex included in it (clearly not working). I would be grateful if someone could tell me an alternative way to highlight these differences please?

Code:

Sub AcidMap()

gFrow = 1
gLastrow = FindLastRow(gcsAmort)
gLastcolumn = FindLastCol(gcsAmort)

gVmyArray = Sheets(gcsAmort).Range(Sheets(gcsAmort).Cells(1, 1), Sheets(gcsAmort).Cells(1, gLastcolumn))
gVAmortArray = Sheets(gcsAmort).Range(Sheets(gcsAmort).Cells(1, 1), Sheets(gcsAmort).Cells(gLastrow, gLastcolumn))

For x = LBound(gVAmortArray) To UBound(gVAmortArray)

    If gVAmortArray(x, 1) <> "ID" Then

            If gVAmortArray(x, 1) = gVAmortArray(x - 1, 1) Then


            For y = 1 To 3

                If y <> 1 Then

                    If gVAmortArray(x, y) <> gVAmortArray(x - 1, y) Then

                        gVAmortArray(x, y).Interior.ColorIndex = 5


                    End If

                End If

             Next y

        End If

    End If

Next x

Sheets(gcsAmort).Range(Sheets(gcsAmort).Cells(1, 1), Sheets(gcsAmort).Cells(gLastrow, gLastcolumn)) = gVAmortArray

MsgBox "Macro Complete"

End Sub

Upvotes: 0

Views: 563

Answers (1)

Scott Craner
Scott Craner

Reputation: 152535

Instead of trying to highlight the array just highlight the cell that corresponds to the value in the array.

Instead of:

gVAmortArray(x, y).Interior.ColorIndex = 5

Use offset anchored to the first cell and using you x and y:

Sheets(gcsAmort).Cells(1, 1).Offset(x-1, y-1).Interior.ColorIndex = 5

And since you are not doing anything to the values in the array there is no need to paste the same values back, so you can remove that line to save a step.

Upvotes: 4

Related Questions