vba to find all occurrences of a string and then...

[andymay]

I'm attempting to design a command that will:
Examine all cells within a given range in my sheet
If the value of that cell equals what I want:
offset one cell down
color the offset cell with an RGB of my choosing

The terms that it's going to search are column headings so in order to color my graphs as desired I need to fill the first cell under the column heading. As many of you may know, graphs won't take their color from cells that are conditionally formatted...

At any rate, my code below just highlights seemingly random cells. It's odd, really. It seems really simple. If anyone has any ideas as to what I'm missing I could really use a 2nd set of eyes here.

<code>
Sub Find_n_Highlight()
Application.ScreenUpdating = False

' begin for/next loop
For Each ce In Range("B3:Y400")
' if the cell's value is management then move down one cell and color that cell turd orange
If ce.Value = "Management" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Interior.Color = RGB(238, 175, 48)
End If
Next ce

Application.ScreenUpdating = True
End Sub
</code>

Thanks all!

[andymay]

You know what, I'm not activating the cell in which the value was found. I'm not sure how... I just figured that out, though so I wanted to share.

[andymay]

All I had to do was post here and I would suddenly become smarter. In case anyone is curious, the loop should be:

<code>
For Each ce In Range("B3:Y400")
If ce.Value = "Management" Then
ce.Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.Interior.Color = RGB(238, 175, 48)
End If
Next ce
</code>

[NoSparks]

I would reduce that to this as there is no need to activate or select anything.

Code: Select all

For Each ce In Range("B3:Y400")
If ce.Value = "Management" Then ce.Offset(1, 0).Interior.Color = RGB(238, 175, 48)
Next ce


If I may make a suggestion....
have a look at Excel/VBA Golden Rules, particularly #1 of the VBA CODE GOLDEN RULES section.
Reason being, you would be forced to declare all variables, then when you typed ce. the VBA intellisense would show a list of all possible things that are acceptable with that variable (and its like having a spell checker).

Andy, when you post code....
you've almost got it, just need to use [ square brackets ] around the words code and /code rather than the < less than and greater than signs > you are using now.
Using the Preview button you can see what the post will look like before committing it with the submit button.

Hope that helps.

[pecoflyer]

@andymay
please wrap your code with code tags. Read the text in red font to learn how. Thx