CODEWITHSUNDEEP
arraysexcelvba
Sylca
Sylca

Reputation: 2545

Loop and compare values from array

In Sheet1 I have a single column with populated cells from 1-20 row with regular numbers. Look at picture bellow:

enter image description here

In Sheet2 I have also a single column, cells are starting from 5-25. If I enter some values into these cells, in Sheet1 from cell with that same value to column "D" background color is changed. Look at pictures bellow to see how it works:

enter image description hereenter image description here

I'm doing it with this piece of code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Row >= 5 Then
    If Target.Offset(0, 0).Text <> "" Then
    Dim n As Long
        For n = 5 To 25
            If Target.Offset(0, 0).Value = Worksheets("Sheet1").Range("A" & n).Value Then
                Worksheets("Sheet1").Range("A" & n & ":D" & n).Interior.ColorIndex = 3
            End If
        Next n
    End If
End If

End Sub

Now, I'd like to make some kind of check every time Sheet1 is activated, in a way if there are no values in cells Interior.ColorIndex = xlNone for all the cells in Range("A1:A20"), if there is a value in some cells Interior.ColorIndex = 3 for those cells. I was thinking about to put these values into an array and then loop through it to compare values but I'm new to VBA so help would be welcome. If there is a better solution, just bring it on.

Also, I'd like to make a piece of code for a situation if I replace value 12 with 17 that Interior.ColorIndex of cell that contains 12 goes to xlNone and of 17 Interior.ColorIndex goes to "3".

So, every suggestion is welcome.

Upvotes: 1

Views: 1369

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

No VBA is needed, as pnuts says.

The easiest way to do this with the colours is with Conditonal Formatting using Formula to check the value of the cells in column A on one sheet against the values in the rows where you want the colour to change and apply the formatting.

See:

BUT if you want VBA, then you can do this in the workbook and worksheet event handlers.

Worksheet Selection/Activation:

in Excel on Sheet1, right-click on the sheetname at the bottom, and click View Code. This will open the class module for Sheet1 in the VB Editor.

At the top of the code module on the left, select Worksheet from the drop-down, and on the right drop-down, click the Activate event-handler.

This will create an empty sub-routine that will be executed by Excel everytime that you select the Sheet1 worksheet.

In that code you can make your checks.

Cell changes on sheet2:

To get code to run everytime you make a cell change on sheet2, you need to open the class module for sheet2, select Worksheet from the drop-down on the left, and Change for the event.

This code will run everytime you change a cell on sheet2 and in here, you would write code that first checks if the Target argument is in your range like this:

If not Application.Intersect(Target, "A5:25") Then Exit Sub

Next you want to write your code to check if the value is no longer matching, and reset the colours.

HTH

Philip

Upvotes: 2

Related Questions