CODEWITHSUNDEEP
vbaexcel
Nathan
Nathan

Reputation: 3

VBA Loop to target certain cells based on criteria

I am currently trying to achieve a macro where it loops through Column D and based on its value i want it to color in certain multiple cells on that specific row where the value exists. This needs to happen for each row where it meets a certain criteria but i can only seem to get it to work when the active cell has been selected not an automated process. Here is what i have so far: 

Sub Validate()

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("D4:D1000")

For Each cell In rng
    If cell.Value = "Building Blocks" Then
        ActiveCell.Offset(, 16).Interior.ColorIndex = 7

    ElseIf cell.Value = "Test" Then
        cell.Interior.ColorIndex = 4

    End If
Next
End Sub

Any Help would be appreciated.

Upvotes: 0

Views: 7781

Answers (3)

rwilson
rwilson

Reputation: 2145

I didn't want to rewrite your code for you, so I just made a few changes to yours:

Sub Validate()

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim counter As Long

Set rng = Range("D4:D1000")
Range("D4").Select

For Each cell In rng


If cell.Value = "Building Blocks" Then
    ActiveCell.Offset(counter, 16).Interior.ColorIndex = 7



ElseIf cell.Value = "Test" Then
    ActiveCell.Offset(counter, 16).Interior.ColorIndex = 4


End If
counter = counter + 1

Next
End Sub

An even better way as noted by 3-14159265358979323846, would be to just take your original code and change Activecell to Cell:

Sub Validate()

Dim rng As Range
Dim row As Range
Dim cell As Range


Set rng = Range("D4:D1000")


For Each cell In rng


If cell.Value = "Building Blocks" Then
    cell.Offset(, 16).Interior.ColorIndex = 7



ElseIf cell.Value = "Test" Then
    cell.Offset(, 16).Interior.ColorIndex = 4


End If


Next
End Sub

Upvotes: 1

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

You are referencing the ActiveCell in the following line ...

ActiveCell.Offset(, 16).Interior.ColorIndex = 7

What you really want is ...

cell.Offset(, 16).Interior.ColorIndex =7

Upvotes: 1

David G
David G

Reputation: 2355

Not sure I understand. This will go through column D, If it finds Building Blocks, it will look in that row, for a second Building Blocks. If it finds it, it will color the cell in colorindex 7. Same idea with find. Not tested.

Sub Validate()

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("D4:D1000")


For Each cell In rng


If cell.Value = "Building Blocks" Then
    for i = 2 to 50 'Howverlong your row is, could go to the end too if dynamic
        if cell(,i).value = cell.value then cell(,i).Interior.ColorIndex = 7
    next



ElseIf cell.Value = "Test" Then
    for i = 2 to 50 'Howverlong your row is, could go to the end too if dynamic
        if cell(,i).value = cell.value then cell(,i).Interior.ColorIndex = 4        
    next


End If

Next
End Sub

Upvotes: 0

Related Questions