Loop and compare values from array

Question

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 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.

@pnuts Actually, I didn't know anything about conditional formatting till now. Now I know so ... ;-) — Sylca
– Sylca, Commented May 23, 2013 at 14:42

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

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

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered May 23, 2013 at 13:26

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sylca Over a year ago

Thank you for the detailed explanation. I've took your suggestion on using conditional formatting, figured out the formula that fits me the best and it worked as I wanted it. Thank you for sharing your knowledge with me!

Collectives™ on Stack Overflow

Loop and compare values from array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related