How to skip through selected cells in EXCEL, using VBA,

Question

I have a VBA macro which selects several cells based on if it contains conditional formatting. These cells won't all be in the same place on each sheet. What I am looking for is a command to skip the activecell to the next cell in the range. The same as pressing TAB on a highlighted range

At the moment I am using sendkeys, as below, however this is messy, and keeps adding Tab spaces in the next line of the vba code (hence the "____Loop")

ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select

Do Until Recount = Count
Recount = Recount + 1
Application.SendKeys "{TAB}", True
    Loop

Any advice would be appreciated

Answer 1

Here's how you can loop over the range:

Dim rng As Range, c As Range

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each c In rng
    c.Select
Next c

It's not clear what the aim of your code is though. What are Count and Recount?

Answer 2

Get a list of selected cells and loop through them

Sub loopThroughCells()
    Dim r as Range
    Set r = Application.Selection
    For i = 0 to r.length
        MsgBox(r.value)
    Next i
End Sub

Suppose three cells with values 1, 2 and 3 are selected. On running the above macro, you will get message boxes with the values 1, 2 and 3 respectively.

Answer 3

If you only need the command for the tab button, just use the .offset(#of rows you want to offset, #of columns you want to offset). So once you know how to locate the cells you need, which you seem to already have, then you can just put.offset(0,1) to move one cell to the right.