CODEWITHSUNDEEP
excelvba
Excel_Geek
Excel_Geek

Reputation: 11

Excel VBA : How to access (Referenced) Ranges from Formula in a Cell

I have a cell (such as B2 in 'Sheet-1') containing a formula like:

='Sheet-2'!B2+'Sheet-3'!B3-'Sheet-4'!B4

Now I want VBA code that can access the references contained in the formula entered in that cell (i.e. B2 in Sheet-1) and then go to those references and make a change in those referred cells.

Upvotes: 1

Views: 123

Answers (1)

Aiken
Aiken

Reputation: 2658

Expanding on z's comment somewhat.

Range(<cell>).Precedents returns a VBA Range representing all cells referred to by <cell> e.g. if cell A1 had the formula =B1+D1 then Range("A1").Precedents = Range("B1,D1")

Expanding this to further fulfil the requirements of your question we can then iterate over the cells within this range using a For Each loop as shown in the example below:

Public Sub AmendPrecedents(fromCell As Range)
    Dim rngReferredTo As Range
    Dim cell As Range

    Set rngReferredTo = fromCell.Precedents

    For Each cell In rngReferredTo
        'Do something with this cell, in this case we're just coloring the background red
        cell.Interior.Color = RGB(255, 0, 0)
    Next cell
End Sub

Upvotes: 1

Related Questions