CODEWITHSUNDEEP
excelvba
Cornelis
Cornelis

Reputation: 435

Run macro when a cell within a certain range is changed

I'm using this:

    Worksheet_Change(ByVal Target As Range)

for several purposes to run a part from my code. Now I'd like to do much the same but only run it when a value within a certain range is changed. I tried the following, but it didn't work:

    Worksheet_Change(ByVal Target As Range("C5:H5"))

The code checks a list of cells one by one, each cell with a value will become green, each empty cell will be coloured red. When doing this one by one you don't need to select cells, but because of the large number of cells to be coloured, I'm looping this part to prevent my code from becoming extremely long.

By only running the code when cells within the certain range are changed, Users from my Excel sheet will still be able to edit other parts of the sheet without having the selected cell to change after every entry.

    Private Sub Worksheet_Change(ByVal Target As Range)


    Sheets("MainSheet").Range("C5").Select
    Do While ActiveCell.Offset(-1, 0) <> ""
        If ActiveCell.Value = "" Then
           ActiveCell.Offset(-1, 0).Interior.Color = RGB(255, 0, 0)
                Else: ActiveCell.Offset(-1, 0).Interior.Color = RGB(0, 255, 0)
        End If
        ActiveCell.Offset(0, 1).Select
    Loop


    End Sub

Upvotes: 1

Views: 3219

Answers (2)

Cornelis
Cornelis

Reputation: 435

Sollution

Before running the actions you want to take, store the active cell

'store currently active cell
Set myActiveCell = ActiveCell
Set myActiveWorksheet = ActiveSheet
Set myActiveWorkbook = ActiveWorkbook

Sheets("MainSheet").Select
Range("C5").Select
j = 0

Do While j < 6
    If ActiveCell.Offset(0, j).Value = "" Then
        ActiveCell.Offset(-1, j).Interior.Color = RGB(255, 0, 0)
            Else: ActiveCell.Offset(-1, j).Interior.Color = RGB(0, 255, 0)
    End If
    j = j + 1
Loop

'go back to previously active cell
    myActiveWorkbook.Activate
    myActiveWorksheet.Activate
    myActiveCell.Activate

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

Use the Target range. ActiveCell will not provide correct result if a value is change in several cells at once (using Ctrl+Enter for instance).

Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("C5:H5")) Is Nothing Then
    MsgBox "Change in " & Target.Address
End If

Upvotes: 1

Related Questions